package cn.fanxin.tools;

import cn.fanxin.utils.MyJdbcUtils;
import com.mysql.jdbc.StringUtils;

import java.sql.Connection;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Author: china-fanxin
 * @Website: https://fan-xin.cn
 * @Date: 2022/11/20 18:33
 * @Description:
 */
public class CommonSql {

    //定义计算公式读取列
    public final static String FORMULATION = "SRC_VALUE_COL";

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成删除对应记录的可执行语句。
     * @Date: 2022/11/23 23:51
     * @Param: []
     * @return: java.lang.String
     */
    public static void genDeleteExecuteSql(String acctDate,HashMap<String,String> kpiConfMap){
        StringBuilder res = new StringBuilder();
        String dateType = CommonFun.dateType(acctDate);
        CommonPrint.printDeletSqlInfo();
        res.append("DELETE FROM ").append(kpiConfMap.get("DST_ENTITY_OWNER")).append(".").append(kpiConfMap.get("DST_ENTITY_NAME")).append(" ")
                .append("\nWHERE ").append(dateType).append(" = '").append(acctDate).append("'\n\t")
                .append("AND KPI_CODE = '").append(kpiConfMap.get("KPI_CODE")).append("'\n;\n");
        System.out.println(res.toString());
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成MC上删除对应记录的可执行语句。（使用事务删除方式）
     *
     * @Date: 2022/11/23 23:51
     * @Param: []
     * @return: java.lang.String
     */
    public static String  genDeleteMcExecuteSql(String acctDate,HashMap<String,String> kpiConfMap){
        StringBuilder res = new StringBuilder();
        String dateType = CommonFun.dateType(acctDate);
        CommonPrint.printDeletSqlInfo();
        res.append("DELETE FROM ").append("ITSY_CUBE.").append(kpiConfMap.get("DST_ENTITY_NAME")).append(" ")
                .append("\nWHERE ").append(dateType).append(" = '").append(acctDate).append("'\n\t")
                .append("AND KPI_CODE = '").append(kpiConfMap.get("KPI_CODE")).append("'\n;\n");
        System.out.println(res.toString());
        return res.toString();
    }


    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成MC清除表分区的语句
     * @Date 2023/4/2 20:32
     * @Param: [acctDate, kpiConfMap]
     * @Return: java.lang.String
     */
    public static String  genDropPartMcExecuteSql(String acctDate,HashMap<String,String> kpiConfMap){
        StringBuilder res = new StringBuilder();
        String dateType = CommonFun.dateType(acctDate);
        CommonPrint.printDeletSqlInfo();
        if ("DM_D_KPI_AL_COMP_S".equals(kpiConfMap.get("DST_ENTITY_NAME"))){
            res.append("ALTER TABLE ").append("ITSY_CUBE.DM_D_KPI_AL_COMP_SUB").append(" DROP IF EXISTS PARTITION (")
                    .append(dateType).append(" = '").append(acctDate).append("',KPI_CODE = '")
                    .append(kpiConfMap.get("KPI_CODE")).append("');\n");
        }else {
            res.append("ALTER TABLE ").append("ITSY_CUBE.").append(kpiConfMap.get("DST_ENTITY_NAME")).append(" DROP IF EXISTS PARTITION (")
                    .append(dateType).append(" = '").append(acctDate).append("',KPI_CODE = '")
                    .append(kpiConfMap.get("KPI_CODE")).append("');\n");
        }
        System.out.println(res.toString());
        return res.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成MC添加分区执行语句
     * @Date 2023/4/2 20:32
     * @Param: [acctDate, kpiConfMap]
     * @Return: java.lang.String
     */
    public static String  genAddPartMcExecuteSql(String acctDate,HashMap<String,String> kpiConfMap){
        StringBuilder res = new StringBuilder();
        String dateType = CommonFun.dateType(acctDate);
        //打印添加分区提示信息
        CommonPrint.printAddPartSqlInfo();
        res.append("ALTER TABLE ").append(genMcTransferTableSql(kpiConfMap)).append(" ADD IF NOT EXISTS PARTITION (")
                .append(dateType).append(" = '").append(acctDate).append("',KPI_CODE = '")
                .append(kpiConfMap.get("KPI_CODE")).append("');\n\n");
        return res.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成删除中间表对应记录的可执行语句。
     * @Date 2023/3/10 11:23
     * @Param: [acctDate, kpiConfMap]
     * @Return: void
     */
    public static void genDeleteMidExecuteSql(String acctDate,HashMap<String,String> kpiConfMap){
        StringBuilder res = new StringBuilder();
        String dateType = CommonFun.dateType(acctDate);
        CommonPrint.printDeletSqlInfo();
        res.append("DELETE FROM ").append(kpiConfMap.get("DST_ENTITY_OWNER")).append(".").append(kpiConfMap.get("DST_ENTITY_NAME")).append("_MID ")
                .append("\nWHERE ").append(dateType).append(" = '").append(acctDate).append("'\n\t")
                .append("AND KPI_CODE = '").append(kpiConfMap.get("KPI_CODE")).append("'\n;\n");
        System.out.println(res.toString());
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成基础指标当期值查询语句
     * @Date: 2022/11/25 11:55
     * @Param: [acctDate, kpiConfMap, etlConfMap]
     * @return: java.lang.String
     */
    public static String genAdbBaseSql(String acctDate, HashMap<String,String> kpiConfMap, HashMap<String,HashMap<Integer,String>> etlConfMap){
        StringBuilder executeSql = new StringBuilder();
        if (CommonCheck.isRelationMap){
            executeSql.append(genAdbBaseSelectSQL(acctDate,kpiConfMap,CommonCheck.dstColList))
                    .append(genBaseWhereSql(acctDate,kpiConfMap))
                    .append(genGroupBySql(CommonCheck.dstColList));
        }else {
            executeSql.append(genAdbBaseSelectSQL(acctDate,kpiConfMap,etlConfMap))
                    .append(genBaseWhereSql(acctDate,kpiConfMap))
                    .append(genGroupBySql(etlConfMap.get("srcColMap")));
        }
        return executeSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成基础指标当期值查询语句（直接从中间表查询）
     * @Date 2023/3/3 16:27
     * @Param: [acctDate, kpiConfMap, etlConfMap]
     * @Return: java.lang.String
     */
    public static String genBaseMcSql(String acctDate,HashMap<String,String> kpiConfMap,HashMap<String,HashMap<Integer,String>> etlConfMap){
        StringBuilder executeSql = new StringBuilder();
        if (CommonCheck.isRelationMap){
            executeSql.append(genBaseMcSelectSQL(kpiConfMap,CommonCheck.dstColList))
                    .append(genBaseMcWhereSql(acctDate,kpiConfMap))
                    .append(genGroupBySql(CommonCheck.dstColList));
        }else {
            executeSql.append(genBaseMcSelectSQL(kpiConfMap,etlConfMap.get("dstColMap")))
                    .append(genBaseMcWhereSql(acctDate,kpiConfMap))
                    .append(genGroupBySql(etlConfMap.get("dstColMap")));
        }
        return executeSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成基础指标当期值查询语句（直接从中间表查询）
     * @Date 2023/3/3 16:27
     * @Param: [acctDate, kpiConfMap, etlConfMap]
     * @Return: java.lang.String
     */
    public static String genMcBaseSql(String acctDate,HashMap<String,String> kpiConfMap,HashMap<String,HashMap<Integer,String>> etlConfMap){
        StringBuilder executeSql = new StringBuilder();
        if (CommonCheck.isRelationMap){
            executeSql.append(genMcBaseSelectSql(acctDate,kpiConfMap,CommonCheck.dstColList))
                    .append(genMcBaseWhereSql(acctDate,kpiConfMap))
                    .append(genMcGroupBySql(acctDate,CommonCheck.dstColList));
        }else {
            executeSql.append(genMcBaseSelectSQL(acctDate,kpiConfMap,etlConfMap))
                    .append(genMcBaseWhereSql(acctDate,kpiConfMap))
                    .append(genMcGroupBySql(acctDate,etlConfMap.get("srcColMap")));
        }
        return executeSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成基础指标可执行语句的方法
     * @Date: 2022/11/22 19:48
     * @Param: [acctDate, kpiConfMap, srcColMap]
     * @return: java.lang.String
     */
    public static String genBaseExecuteSql(String acctDate,HashMap<String,String> kpiConfMap,HashMap<String,HashMap<Integer,String>> etlConfMap){
        StringBuilder executeSql = new StringBuilder();
        executeSql.append(genInsertSQL(kpiConfMap))
                .append(genAdbBaseSelectSQL(acctDate,kpiConfMap,etlConfMap))
                .append(genBaseWhereSql(acctDate,kpiConfMap))
                .append(genGroupBySql(etlConfMap.get("srcColMap"))).append("\n;");
        return executeSql.toString().toUpperCase();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成基础指标可执行语句的方法 （带有自定义公式列表）
     * @Date: 2022/11/22 19:48
     * @Param: [acctDate, kpiConfMap, srcColMap]
     * @return: java.lang.String
     */
    public static String genBaseDefineExecuteSql(String acctDate,HashMap<String,String> kpiConfMap,Properties confProp){
        //生成删除历史账期的语句
        CommonSql.genDeleteExecuteSql(acctDate, kpiConfMap);
        CommonPrint.printBaseSqlStart();
        StringBuilder executeSql = new StringBuilder();
        HashMap<String, HashMap<Integer, String>> etlConfMap = CommonCheck.etlConfMap;
        executeSql.append(genDefineInsertSQL(acctDate,kpiConfMap,etlConfMap.get("dstColMap")))
                .append(genBaseDefineSelectSql(acctDate,kpiConfMap,etlConfMap,confProp))
                .append(genBaseDefineConversionSelectSql(acctDate,kpiConfMap,etlConfMap).replaceAll("[\\n]","\n\t"))
                .append(genBaseDefineUnionAllSql(acctDate,kpiConfMap,etlConfMap).replaceAll("[\\n]","\n\t\t"))
                .append("\n\t)").append(genGroupBySql(etlConfMap.get("dstColMap")).replaceAll("[\\n]","\n\t")).append("\n)")
                .append(genGroupBySql(etlConfMap.get("dstColMap"))).append("\n;");
        return executeSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成MC上加工基础指标的可执行语句（包含衍生值并且直接插入目标表）
     * @Date 2023/4/1 20:49
     * @Param: [acctDate, kpiConfMap, confProp]
     * @Return: java.lang.String
     */
    public static String genBaseMCDefineExecuteSql(String acctDate,HashMap<String,String> kpiConfMap,Properties confProp){
        StringBuilder executeSql = new StringBuilder();
        CommonPrint.printBaseSqlStart();
        String addPartSql = CommonSql.genAddPartMcExecuteSql(acctDate, kpiConfMap);
        executeSql.append(addPartSql);
        HashMap<String, HashMap<Integer, String>> etlConfMap = CommonCheck.etlConfMap;
        executeSql.append(genMcDefineInsertOverwriteSQL(acctDate,kpiConfMap,etlConfMap.get("dstColMap")))
                .append(genMcBaseDefineSelectSql(acctDate,kpiConfMap,etlConfMap,confProp))
                .append(genMcBaseDefineConversionSelectSql(acctDate,kpiConfMap,etlConfMap).replaceAll("[\\n]","\n\t"))
                .append(genMcBaseDefineUnionAllSql(acctDate,kpiConfMap,etlConfMap).replaceAll("[\\n]","\n\t\t"))
                .append("\n\t)").append(genGroupBySql(etlConfMap.get("dstColMap")).replaceAll("[\\n]","\n\t")).append("\n)")
                .append(genGroupBySql(etlConfMap.get("dstColMap"))).append(";\n\n");
        return executeSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成基础指标可执行语句的方法 （基于中间表的当期值，带有自定义公式列表）
     * @Date 2023/3/3 16:24
     * @Param: [acctDate, kpiConfMap, confProp]
     * @Return: java.lang.String
     */
    public static String genBaseMc2AdbDefineExecuteSql(String acctDate, HashMap<String,String> kpiConfMap, Properties confProp){
        //生成删除历史账期的语句
        CommonSql.genDeleteExecuteSql(acctDate, kpiConfMap);
        CommonPrint.printBaseSqlStart();
        StringBuilder executeSql = new StringBuilder();
        HashMap<String, HashMap<Integer, String>> etlConfMap = CommonCheck.etlConfMap;
        executeSql.append(genDefineInsertSQL(acctDate,kpiConfMap,etlConfMap.get("dstColMap")))
                .append(genBaseMc2AdbDefineSelectSql(acctDate,kpiConfMap,etlConfMap,confProp))
                .append(genBaseMC2AdbDefineConversionSelectSql(acctDate,kpiConfMap,etlConfMap).replaceAll("[\\n]","\n\t"))
                .append(genBaseMcDefineUnionAllSql(acctDate,kpiConfMap,etlConfMap).replaceAll("[\\n]","\n\t\t"))
                .append("\n\t)").append(genGroupBySql(etlConfMap.get("dstColMap")).replaceAll("[\\n]","\n\t")).append("\n)")
                .append(genGroupBySql(etlConfMap.get("dstColMap"))).append("\n;");
        return executeSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成计算指标可执行语句的方法
     * @Date: 2022/11/23 21:35
     * @Param: [acctDate, kpiConfMap, etlConfMap, confProp]
     * @return: java.lang.String
     */
    public static String genJSExecuteSql(String acctDate, HashMap<String, String> kpiConfMap,HashMap<String,HashMap<Integer,String>> etlConfMap,Properties confProp) {
        StringBuilder executeSql = new StringBuilder();
        executeSql.append("\nSELECT\n\t");
        for (int i=0;i<etlConfMap.get("srcColMap").size();i++){
            executeSql.append(etlConfMap.get("srcColMap").get(i)).append(",\n\t");
        }
        executeSql.append("'").append(kpiConfMap.get("KPI_CODE")).append("'").append(" AS KPI_CODE,\n\t")
                .append("CAST(SUM(").append(kpiConfMap.get(FORMULATION).replaceAll("[\\{\\}]","`")).append(") AS DECIMAL(54,18)) AS KPI_VALUE \nFROM (\n\t");
        executeSql.append(genJSConversionSelectSql(kpiConfMap,etlConfMap).replaceAll("[\\n]","\n\t"))
                .append(genJSUnionAllSql(acctDate,kpiConfMap,confProp).replaceAll("[\\n]","\n\t\t"))
                .append("\n\t)").append(genGroupBySql(etlConfMap.get("srcColMap")).replaceAll("[\\n]","\n\t")).append("\n)\t")
                .append(genGroupBySql(etlConfMap.get("srcColMap")))
                .append("\n;");
        return executeSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成计算指标可执行语句的方法
     * @Date: 2022/11/23 21:35
     * @Param: [acctDate, kpiConfMap, etlConfMap, confProp]
     * @return: java.lang.String
     */
    public static String genJSDefineExecuteSql(String acctDate, HashMap<String, String> kpiConfMap,HashMap<Integer,String> jsEtlConf,Properties confProp) {
        //生成删除历史账期数据的语句
        CommonSql.genDeleteExecuteSql(acctDate, kpiConfMap);
        CommonPrint.printJsSqlStart();
        StringBuilder executeSql = new StringBuilder();
        executeSql.append(genDefineInsertSQL(acctDate,kpiConfMap,jsEtlConf))
                .append(genJSDefineSelectSql(acctDate,kpiConfMap,jsEtlConf))
                .append(genJSDefineConversionSelectSql(acctDate,kpiConfMap,jsEtlConf).replaceAll("[\\n]","\n\t"))
                .append(genJSDefineUnionAllSql(acctDate,kpiConfMap,confProp).replaceAll("[\\n]","\n\t\t"))
                .append("\n\t) ").append(genGroupBySql(jsEtlConf).replaceAll("[\\n]","\n\t")).append("\n)")
                .append(genGroupBySql(jsEtlConf)).append("\n;");
        return executeSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成MC上计算指标可执行语句的方法
     * @Date 2023/4/3 12:10
     * @Param: [acctDate, kpiConfMap, jsEtlConf, confProp]
     * @Return: java.lang.String
     */
    public static String genMcJSDefineExecuteSql(String acctDate, HashMap<String, String> kpiConfMap,HashMap<Integer,String> jsEtlConf,Properties confProp) {
        CommonPrint.printJsSqlStart();
        StringBuilder executeSql = new StringBuilder();
        executeSql.append(genAddPartMcExecuteSql(acctDate,kpiConfMap));
        executeSql.append(genMcDefineInsertOverwriteSQL(acctDate,kpiConfMap,jsEtlConf))
                .append(genMcJSDefineSelectSql(acctDate,kpiConfMap,jsEtlConf))
                .append(genJSDefineConversionSelectSql(acctDate,kpiConfMap,jsEtlConf).replaceAll("[\\n]","\n\t"))
                .append(genMcJSDefineUnionAllSql(acctDate,kpiConfMap,confProp).replaceAll("[\\n]","\n\t\t"))
                .append("\n\t) ").append(genGroupBySql(jsEtlConf).replaceAll("[\\n]","\n\t")).append("\n)")
                .append(genGroupBySql(jsEtlConf)).append(";\n");
        return executeSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成自定义衍生值公式列表
     * 日指标部分：
     * 1.昨日值 {KPI_CODE,NOR,0,0,-1}
     * 2.本月累计 {KPI_CODE,SUM,0,0,ST}
     * 3.上月同期 {KPI_CODE,NOR,0,-1,0}
     * 4.上月同期累计 {KPI_CODE,SUM,0,-1,ST}
     * 5。去年同期值  {KPI_CODE,NOR,-1,0,0}
     * 6.去年同期累计值 {KPI_CODE,SUM,-1,0,ST}
     * 7.本月日均 {KPI_CODE,AVG,0,0,ST}
     * 8.上月日均 {KPI_CODE,AVG,0,-1,ST}
     * 将以上公式添加到一个自定义的列表中，进行指标加工时，自动进行以上公式的加工及拼接，生成所需的衍生值
     * 月指标部分：昨日值、本月累计、上月同期、上月同期累计、去年同期值、去年同期累计值、本月日均、上月日均
     * 1.上月值 {KPI_CODE,NOR,0,-1}
     * 2.本年累计值 {KPI_CODE,SUM,0,ST}
     * 3.上年同期值 {KPI_CODE,NOR,-1,0}
     * 4.上年同期年累计值 {KPI_CODE,SUM,-1,ST}
     * 5.上年12月值 {KPI_CODE,NOR,-1,EN}
     * @Date: 2022/11/24 11:17
     * @Param: [acctDate, kpiConfMap, etlConfMap]
     * @return: java.util.List<java.lang.String>
     */
    public static List<String> genDefineFormularList(String acctDate,HashMap<String,String> kpiConfMap){
        String kpiCode = kpiConfMap.get("KPI_CODE");
        //自定义衍生值公式列表
        List<String> defineFormularList = new ArrayList<>();
        //初始化日指标衍生值列表
        if (acctDate.length()==8){
            defineFormularList.add(kpiCode+",NOR,0,0,-1");
            defineFormularList.add(kpiCode+",SUM,0,0,ST");
            defineFormularList.add(kpiCode+",NOR,0,-1,0");
            defineFormularList.add(kpiCode+",SUM,0,-1,ST");
            defineFormularList.add(kpiCode+",NOR,-1,0,0");
            defineFormularList.add(kpiCode+",SUM,-1,0,ST");
            defineFormularList.add(kpiCode+",AVG,0,0,ST");
            defineFormularList.add(kpiCode+",AVG,0,-1,ST");
        }else if (acctDate.length()==6){
            defineFormularList.add(kpiCode+",NOR,0,-1");
            defineFormularList.add(kpiCode+",SUM,0,ST");
            defineFormularList.add(kpiCode+",NOR,-1,0");
            defineFormularList.add(kpiCode+",SUM,-1,ST");
            defineFormularList.add(kpiCode+",NOR,-1,EN");
        }
        return defineFormularList;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成自定义衍生值公式列表（去除平均数计算）
     * 日指标部分：
     * 1.昨日值 {KPI_CODE,NOR,0,0,-1}
     * 2.本月累计 {KPI_CODE,SUM,0,0,ST}
     * 3.上月同期 {KPI_CODE,NOR,0,-1,0}
     * 4.上月同期累计 {KPI_CODE,SUM,0,-1,ST}
     * 5。去年同期值  {KPI_CODE,NOR,-1,0,0}
     * 6.去年同期累计值 {KPI_CODE,SUM,-1,0,ST}
     * 7.本月日均 {KPI_CODE,AVG,0,0,ST}  由于首次刷数的时候，最内层限定账期区间到当期无法的当期数据在目标表还不存在，
     * 所以无法进行月初到当期的平均值计算，只能在外层继续嵌套一层，故此删除自定义公式列表中关于当期平均数的计算
     * 8.上月日均 {KPI_CODE,AVG,0,-1,ST}
     * 将以上公式添加到一个自定义的列表中，进行指标加工时，自动进行以上公式的加工及拼接，生成所需的衍生值
     * 月指标部分：昨日值、本月累计、上月同期、上月同期累计、去年同期值、去年同期累计值、本月日均、上月日均
     * 1.上月值 {KPI_CODE,NOR,0,-1}
     * 2.本年累计值 {KPI_CODE,SUM,0,ST}
     * 3.上年同期值 {KPI_CODE,NOR,-1,0}
     * 4.上年同期年累计值 {KPI_CODE,SUM,-1,ST}
     * 5.上年12月值 {KPI_CODE,NOR,-1,EN}
     * @Date: 2023/04/01 14:40
     * @Param: [acctDate, kpiConfMap, etlConfMap]
     * @return: java.util.List<java.lang.String>
     */
    public static List<String> genDefineNotContainsCurrentAvgFormularList(String acctDate,HashMap<String,String> kpiConfMap){
        String kpiCode = kpiConfMap.get("KPI_CODE");
        //自定义衍生值公式列表
        List<String> defineFormularList = new ArrayList<>();
        //初始化日指标衍生值列表
        if (acctDate.length()==8){
            defineFormularList.add(kpiCode+",NOR,0,0,-1");
            defineFormularList.add(kpiCode+",SUM,0,0,ST");
            defineFormularList.add(kpiCode+",NOR,0,-1,0");
            defineFormularList.add(kpiCode+",SUM,0,-1,ST");
            defineFormularList.add(kpiCode+",NOR,-1,0,0");
            defineFormularList.add(kpiCode+",SUM,-1,0,ST");
            defineFormularList.add(kpiCode+",AVG,0,-1,ST");
        }else if (acctDate.length()==6){
            defineFormularList.add(kpiCode+",NOR,0,-1");
            defineFormularList.add(kpiCode+",SUM,0,ST");
            defineFormularList.add(kpiCode+",NOR,-1,0");
            defineFormularList.add(kpiCode+",SUM,-1,ST");
            defineFormularList.add(kpiCode+",NOR,-1,EN");
        }
        return defineFormularList;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 若指标配置中的IS_SUM不为1，则该指标是一个时期值，衍生值中的累计值都是当期值，特定义一套自定义公式，当IS_SUM不等于1时调用，去除当期值的平均值
     * @Date: 2023/04/01 23:38
     * @Param: [acctDate, kpiConfMap]
     * @return: java.util.List<java.lang.String>
     */
    public static List<String> genDefineIsNotSumNotContainsCurrentAvgFormularList(String acctDate,HashMap<String,String> kpiConfMap){
        String kpiCode = kpiConfMap.get("KPI_CODE");
        //自定义衍生值公式列表
        List<String> defineFormularList = new ArrayList<>();
        //初始化日指标衍生值列表
        if (acctDate.length()==8){
            defineFormularList.add(kpiCode+",NOR,0,0,-1");
            defineFormularList.add(kpiCode+",NOR,0,-1,0");
            defineFormularList.add(kpiCode+",NOR,-1,0,0");
            defineFormularList.add(kpiCode+",AVG,0,-1,ST");
        }else if (acctDate.length()==6){
            defineFormularList.add(kpiCode+",NOR,0,-1");
            defineFormularList.add(kpiCode+",NOR,-1,0");
            defineFormularList.add(kpiCode+",NOR,-1,EN");
        }
        return defineFormularList;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 若指标配置中的IS_SUM不为1，则该指标是一个时期值，衍生值中的累计值都是当期值，特定义一套自定义公式，当IS_SUM不等于1时调用
     * @Date: 2022/11/27 23:38
     * @Param: [acctDate, kpiConfMap]
     * @return: java.util.List<java.lang.String>
     */
    public static List<String> genDefineIsNotSumFormularList(String acctDate,HashMap<String,String> kpiConfMap){
        String kpiCode = kpiConfMap.get("KPI_CODE");
        //自定义衍生值公式列表
        List<String> defineFormularList = new ArrayList<>();
        //初始化日指标衍生值列表
        if (acctDate.length()==8){
            defineFormularList.add(kpiCode+",NOR,0,0,-1");
            defineFormularList.add(kpiCode+",NOR,0,-1,0");
            defineFormularList.add(kpiCode+",NOR,-1,0,0");
            defineFormularList.add(kpiCode+",AVG,0,0,ST");
            defineFormularList.add(kpiCode+",AVG,0,-1,ST");
        }else if (acctDate.length()==6){
            defineFormularList.add(kpiCode+",NOR,0,-1");
            defineFormularList.add(kpiCode+",NOR,-1,0");
            defineFormularList.add(kpiCode+",NOR,-1,EN");
        }
        return defineFormularList;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成通用的 insert 的SQL部分
     * @Date: 2022/11/20 18:34
     * @Param: []
     * @return: java.lang.String
     */
    public static String genInsertSQL(HashMap<String,String> kpiConfMap){
        StringBuilder insertSql = new StringBuilder();
        insertSql.append("/*+ QUERY_TIMEOUT=600000 */INSERT INTO ").append(kpiConfMap.get("DST_ENTITY_OWNER")).append(".").append(kpiConfMap.get("DST_ENTITY_NAME"));
        return insertSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成通用的 insert 的SQL部分 (带 自定义列表)
     * @Date: 2022/11/23 23:26
     * @Param: [dstTable, etlConfMap]
     * @return: java.lang.String
     */
    public static String genDefineInsertSQL(String acctDate,HashMap<String,String> kpiConfMap,HashMap<Integer, String> jsEtlConf) {
        StringBuilder insertSql = new StringBuilder();
        insertSql.append("/*+ QUERY_TIMEOUT=600000 */INSERT INTO ").append(kpiConfMap.get("DST_ENTITY_OWNER")).append(".").append(kpiConfMap.get("DST_ENTITY_NAME")).append(" (\n\t");
        if (CommonCheck.isRelationMap){
            CommonCheck.dstColList.stream().forEach(dstCol->{
                insertSql.append(dstCol).append(",\n\t");
            });
        }else{
            for (int i=0;i<jsEtlConf.size();i++){
                insertSql.append(jsEtlConf.get(i)).append(",\n\t");
            }
        }
        if (acctDate.length()==8){
            insertSql.append("KPI_CODE,\n\t")
                    .append("KPI_VALUE,\n\t")
                    .append("D_LD_VALUE,\n\t")
                    .append("M_TM_VALUE,\n\t")
                    .append("D_LM_VALUE,\n\t")
                    .append("M_LM_VALUE,\n\t")
                    .append("D_LY_VALUE,\n\t")
                    .append("M_LY_VALUE,\n\t")
                    .append("M_DA_VALUE,\n\t")
                    .append("M_LDA_VALUE\n )");
        }else if (acctDate.length()==6){
            insertSql.append("KPI_CODE,\n\t")
                    .append("KPI_VALUE,\n\t")
                    .append("M_LM_VALUE,\n\t")
                    .append("Y_TY_VALUE,\n\t")
                    .append("M_LY_VALUE,\n\t")
                    .append("Y_LY_VALUE,\n\t")
                    .append("M_LY12_VALUE\n )");
        }
        return insertSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成通用的 insert 的SQL部分（MC指标分区表）
     * @Date: 2022/11/23 23:26
     * @Param: [dstTable, etlConfMap]
     * @return: java.lang.String
     */
    public static String genMcDefineInsertSQL(String acctDate,HashMap<String,String> kpiConfMap,HashMap<Integer, String> jsEtlConf) {
        String dateType = CommonFun.dateType(acctDate);
        StringBuilder insertSql = new StringBuilder();
        insertSql.append("INSERT INTO ").append(genMcTransferTableSql(kpiConfMap))
                .append(" PARTITION (").append(dateType).append(" = '").append(acctDate).append("',KPI_CODE = '").append(kpiConfMap.get("KPI_CODE")).append("')\n");
        return insertSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成通用的 insert 的SQL部分（MC指标分区表）
     * @Date: 2022/11/23 23:26
     * @Param: [dstTable, etlConfMap]
     * @return: java.lang.String
     */
    public static String genMcDefineInsertOverwriteSQL(String acctDate,HashMap<String,String> kpiConfMap,HashMap<Integer, String> jsEtlConf) {
        String dateType = CommonFun.dateType(acctDate);
        StringBuilder insertSql = new StringBuilder();
        insertSql.append("INSERT OVERWRITE TABLE ").append(genMcTransferTableSql(kpiConfMap))
                .append(" PARTITION (").append(dateType).append(" = '").append(acctDate).append("',KPI_CODE = '").append(kpiConfMap.get("KPI_CODE")).append("')\n");
        return insertSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成通用的 insert Mid表的SQL部分 (带 自定义列表)
     * @Date: 2022/11/23 23:26
     * @Param: [dstTable, etlConfMap]
     * @return: java.lang.String
     */
    public static String genDefineInsertMidSQL(String acctDate,HashMap<String,String> kpiConfMap,HashMap<Integer, String> jsEtlConf) {
        StringBuilder insertSql = new StringBuilder();
        insertSql.append("/*+ QUERY_TIMEOUT=600000 */INSERT INTO ").append(kpiConfMap.get("DST_ENTITY_OWNER")).append(".").append(kpiConfMap.get("DST_ENTITY_NAME")).append("_MID (\n\t");
        if (CommonCheck.isRelationMap){
            CommonCheck.dstColList.stream().forEach(dstCol->{
                insertSql.append(dstCol).append(",\n\t");
            });
        }else{
            for (int i=0;i<jsEtlConf.size();i++){
                insertSql.append(jsEtlConf.get(i)).append(",\n\t");
            }
        }
        if (acctDate.length()==8){
            insertSql.append("KPI_CODE,\n\t")
                    .append("KPI_VALUE\n\t) ");
        }else if (acctDate.length()==6){
            insertSql.append("KPI_CODE,\n\t")
                    .append("KPI_VALUE\n\t) ");
        }
        return insertSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据配置表生成基础指标 select 语句部分
     * @Date: 2022/11/22 19:46
     * @Param: [kpiConfMap, srcColMap]
     * @return: java.lang.String
     */
    public static String genBaseMcSelectSQL(HashMap<String,String> kpiConfMap,HashMap<Integer,String> srcColMap){
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("\nSELECT\n\t");
        for(int i=0;i<srcColMap.size();i++){
            selectSql.append(srcColMap.get(i)).append(",\n\t");
        }
        selectSql.append("'"+kpiConfMap.get("KPI_CODE")+"' AS KPI_CODE,")
                .append("\n\tCAST(SUM(KPI_VALUE) AS DECIMAL(54,18)) AS KPI_VALUE ")
                .append("\nFROM ").append(kpiConfMap.get("DST_ENTITY_OWNER")).append(".").append(kpiConfMap.get("DST_ENTITY_NAME")).append("_MID");
        return selectSql.toString();
    }


    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据配置表生成基础指标 select 语句部分
     * @Date 2023/4/11 22:19
     * @Param: [acctDate, kpiConfMap, etlConfMap]
     * @Return: java.lang.String
     */
    public static String genMcBaseSelectSQL(String acctDate,HashMap<String,String> kpiConfMap,HashMap<String,HashMap<Integer,String>> etlConfMap){
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("\nSELECT\n\t");
        for(int i=0;i<etlConfMap.get("srcColMap").size();i++){
            if ("DATE_ID".equals(etlConfMap.get("srcColMap").get(i))){
                selectSql.append("'").append(acctDate).append("' AS DATE_ID,\n\t");
            }else if ("MONTH_ID".equals(etlConfMap.get("srcColMap").get(i))){
                selectSql.append("'").append(acctDate.substring(0,6)).append("' AS MONTH_ID,\n\t");
            }else if ("DAY_ID".equals(etlConfMap.get("srcColMap").get(i))){
                selectSql.append("'").append(acctDate.substring(6,8)).append("' AS DAY_ID,\n\t");
            }else {
                selectSql.append(etlConfMap.get("srcColMap").get(i)).append(" AS ").append(etlConfMap.get("dstColMap").get(i)).append(",\n\t");
            }
        }
        selectSql.append("'"+kpiConfMap.get("KPI_CODE")+"' AS KPI_CODE,\n\t")
                .append("CAST(SUM("+kpiConfMap.get("SRC_VALUE_COL")+") AS DECIMAL(38,18)) AS KPI_VALUE \n")
                .append("FROM ").append("ITSY_CUBE.").append(kpiConfMap.get("SRC_ENTITY_NAME"));
        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据配置表生成基础指标 select 语句部分MC（MC平台部分）
     * @Date: 2022/11/22 19:46
     * @Param: [kpiConfMap, srcColMap]
     * @return: java.lang.String
     */
    public static String genBaseMCSelectSQL(HashMap<String,String> kpiConfMap,HashMap<Integer,String> srcColMap){
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("\nSELECT\n\t");
        for(int i=0;i<srcColMap.size();i++){
            selectSql.append(srcColMap.get(i)).append(",\n\t");
        }
        selectSql.append("'"+kpiConfMap.get("KPI_CODE")+"' AS KPI_CODE,")
                .append("\n\tCAST(SUM("+kpiConfMap.get("SRC_VALUE_COL")+") AS DECIMAL(38,18)) AS KPI_VALUE ")
                .append("\nFROM ").append(kpiConfMap.get("SRC_ENTITY_NAME")).append(" ");
        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据配置表生成基础指标 select 语句部分
     * @Date: 2022/11/22 19:46
     * @Param: [kpiConfMap, srcColMap]
     * @return: java.lang.String
     */
    public static String genAdbBaseSelectSQL(String acctDate,HashMap<String,String> kpiConfMap, List<String> dstColList){
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("\nSELECT\n\t");
        dstColList.stream().forEach(dstCol->{
            if ("DATE_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate).append("' AS DATE_ID,\n\t");
            }else if ("MONTH_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate.substring(0,6)).append("' AS MONTH_ID,\n\t");
            }else if ("DAY_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate.substring(6,8)).append("' AS DAY_ID,\n\t");
            }else {
                selectSql.append(dstCol).append(",\n\t");
            }
        });
        selectSql.append("'"+kpiConfMap.get("KPI_CODE")+"' AS KPI_CODE,")
                .append("\n\tCAST(SUM("+kpiConfMap.get("SRC_VALUE_COL")+") AS DECIMAL(54,18)) AS KPI_VALUE ")
                .append("\nFROM ").append(kpiConfMap.get("SRC_ENTITY_OWNER")).append(".").append(kpiConfMap.get("SRC_ENTITY_NAME")).append(" ");
        return selectSql.toString();
    }


    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据配置表生成基础指标 select 语句部分
     * 2023.04.04:修改方法的参数传递，多维指标通过ETL配置表进行最内层的重命名功能添加
     * @Date: 2022/11/22 19:46
     * @Param: [kpiConfMap, srcColMap]
     * @return: java.lang.String
     */
    public static String genAdbBaseSelectSQL(String acctDate,HashMap<String,String> kpiConfMap, HashMap<String, HashMap<Integer, String>> etlConfMap){
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("\nSELECT\n\t");
        for(int i = 0; i< etlConfMap.get("srcColMap").size(); i++){
            if ("DATE_ID".equals(etlConfMap.get("srcColMap").get(i))){
                selectSql.append("'").append(acctDate).append("' AS DATE_ID,\n\t");
            }else if ("MONTH_ID".equals(etlConfMap.get("srcColMap").get(i))){
                selectSql.append("'").append(acctDate.substring(0,6)).append("' AS MONTH_ID,\n\t");
            }else if ("DAY_ID".equals(etlConfMap.get("srcColMap").get(i))){
                selectSql.append("'").append(acctDate.substring(6,8)).append("' AS DAY_ID,\n\t");
            }else {
                selectSql.append(etlConfMap.get("srcColMap").get(i)).append(" AS ").append(etlConfMap.get("dstColMap").get(i)).append(",\n\t");
            }
        }
        selectSql.append("'"+kpiConfMap.get("KPI_CODE")+"' AS KPI_CODE,\n\t")
                .append("CAST(SUM("+kpiConfMap.get("SRC_VALUE_COL")+") AS DECIMAL(54,18)) AS KPI_VALUE \n")
                .append("FROM ").append(kpiConfMap.get("SRC_ENTITY_OWNER")).append(".").append(kpiConfMap.get("SRC_ENTITY_NAME")).append(" ");
        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据配置表生成基础指标 select 语句部分 (adb平台基于中间表本身具备的当期值）
     * @Date: 2022/11/22 19:46
     * @Param: [kpiConfMap, srcColMap]
     * @return: java.lang.String
     */
    public static String genBaseMcSelectSQL(HashMap<String,String> kpiConfMap,List<String> dstColList){
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("\nSELECT\n\t");
        dstColList.stream().forEach(dstCol->{
            selectSql.append(dstCol).append(",\n\t");
        });
        selectSql.append("'"+kpiConfMap.get("KPI_CODE")+"' AS KPI_CODE,")
                .append("\n\tCAST(SUM(KPI_VALUE) AS DECIMAL(54,18)) AS KPI_VALUE")
                .append("\nFROM ").append(kpiConfMap.get("DST_ENTITY_OWNER")).append(".").append(kpiConfMap.get("DST_ENTITY_NAME")).append("_MID");
        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据配置表生成基础指标 select 语句部分（MC平台部分）
     * @Date 2023/3/3 9:21
     * @Param: [kpiConfMap, dstColList]
     * @Return: java.lang.String
     */
    public static String genBaseMCSelectSQL(HashMap<String,String> kpiConfMap,List<String> dstColList){
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("\nSELECT\n\t");
        dstColList.stream().forEach(dstCol->{
            selectSql.append(dstCol).append(",\n\t");
        });
        selectSql.append("'"+kpiConfMap.get("KPI_CODE")+"' AS KPI_CODE,")
                .append("\n\tCAST(SUM("+kpiConfMap.get("SRC_VALUE_COL")+") AS DECIMAL(36,18)) AS KPI_VALUE ")
                .append("\nFROM ").append(kpiConfMap.get("SRC_ENTITY_NAME")).append(" ");
        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据配置表生成基础指标 select 语句部分（MC平台部分）
     * @Date 2023/3/3 9:21
     * @Param: [kpiConfMap, dstColList]
     * @Return: java.lang.String
     */
    public static String genMcBaseSelectSql(String acctDate,HashMap<String,String> kpiConfMap,List<String> dstColList){
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("\nSELECT\n\t");
        dstColList.stream().forEach(dstCol->{
            if ("DATE_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate).append("' AS DATE_ID,\n\t");
            }else if ("MONTH_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate.substring(0,6)).append("' AS MONTH_ID,\n\t");
            }else if ("DAY_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate.substring(6,8)).append("' AS DAY_ID,\n\t");
            }else {
                selectSql.append(dstCol).append(",\n\t");
            }
        });
        selectSql.append("'"+kpiConfMap.get("KPI_CODE")+"' AS KPI_CODE,\n\t")
                .append("CAST(SUM("+kpiConfMap.get("SRC_VALUE_COL")+") AS DECIMAL(38,18)) AS KPI_VALUE \n")
                .append("FROM ITSY_CUBE.").append(kpiConfMap.get("SRC_ENTITY_NAME"));
        return selectSql.toString();
    }


    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成自定义公式列表的最外层 select 语句
     * @Date: 2022/11/25 12:39
     * @Param: [acctDate, kpiConfMap, etlConfMap, confProp]
     * @return: java.lang.String
     */
    public static String  genBaseDefineSelectSql(String acctDate, HashMap<String, String> kpiConfMap,HashMap<String,HashMap<Integer,String>> etlConfMap,Properties confProp){
        StringBuilder selectSql = new StringBuilder();
        String kpiCode = kpiConfMap.get("KPI_CODE");
        long timeDistance = CommonFun.getTimeDistance(acctDate,"KPI_CODE,AVG,0,0,ST");
        selectSql.append("\nSELECT\n\t");
        if (CommonCheck.isRelationMap){
            CommonCheck.dstColList.stream().forEach(dstCol->{
                selectSql.append(dstCol).append(",\n\t");
            });
        }else {
            for (int i=0;i<etlConfMap.get("dstColMap").size();i++){
                selectSql.append(etlConfMap.get("dstColMap").get(i)).append(",\n\t");
            }
        }
        selectSql.append("'").append(kpiConfMap.get("KPI_CODE")).append("'").append(" AS KPI_CODE,");
        if ("1".equals(kpiConfMap.get("IS_SUM"))){
            if (acctDate.length()==8){
                selectSql.append("\n\tCAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(54,18)) AS KPI_VALUE,/*当日值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,0,-1").append("`) AS DECIMAL(54,18)) AS D_LD_VALUE, /*昨日值*/")
                        .append("\n\tCAST(SUM(").append("NVL(`").append(kpiCode+",SUM,0,0,ST`,0)+NVL(`").append(kpiCode).append("`,0)) AS DECIMAL(54,18)) AS M_TM_VALUE, /*本月累计值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1,0").append("`) AS DECIMAL(54,18)) AS D_LM_VALUE, /*上月同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,0,-1,ST").append("`) AS DECIMAL(54,18)) AS M_LM_VALUE, /*上月同期累计值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0,0").append("`) AS DECIMAL(54,18)) AS D_LY_VALUE, /*去年同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,-1,0,ST").append("`) AS DECIMAL(54,18)) AS M_LY_VALUE, /*去年同期累计值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,0,0,ST`+`").append(kpiCode).append("`)/").append(timeDistance+1).append(" AS DECIMAL(54,18)) AS M_DA_VALUE, /*本月日均*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",AVG,0,-1,ST").append("`) AS DECIMAL(54,18)) AS M_LDA_VALUE /*上月日均*/")
                        .append("\nFROM (\n\t");
            }else if (acctDate.length()==6){
                selectSql.append("\n\tCAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(54,18)) AS KPI_VALUE, /*当月值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1").append("`) AS DECIMAL(54,18)) AS M_LM_VALUE, /*上月值*/")
                        .append("\n\tCAST(SUM(").append("NVL(`").append(kpiCode+",SUM,0,ST`,0)+NVL(`").append(kpiCode).append("`,0)) AS DECIMAL(54,18)) AS Y_TY_VALUE, /*本年累计值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0").append("`) AS DECIMAL(54,18)) AS M_LY_VALUE, /*上年同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,-1,ST").append("`) AS DECIMAL(54,18)) AS Y_LY_VALUE, /*上年同期年累计值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,EN").append("`) AS DECIMAL(54,18)) AS M_LY12_VALUE  /*上年12月值*/")
                        .append("\nFROM (\n\t");
            }
        }else {
            if (acctDate.length()==8){
                selectSql.append("\n\tCAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(54,18)) AS KPI_VALUE,/*当日值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,0,-1").append("`) AS DECIMAL(54,18)) AS D_LD_VALUE, /*昨日值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(54,18)) AS M_TM_VALUE, /*本月累计值(即本期值）*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1,0").append("`) AS DECIMAL(54,18)) AS D_LM_VALUE, /*上月同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1,0").append("`) AS DECIMAL(54,18)) AS M_LM_VALUE, /*上月同期累计值（即上月同期值）*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0,0").append("`) AS DECIMAL(54,18)) AS D_LY_VALUE, /*去年同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0,0").append("`) AS DECIMAL(54,18)) AS M_LY_VALUE, /*去年同期累计值（即去年同期值）*/")
                        .append("\n\tNULL AS M_DA_VALUE, /*本月日均*/")
                        .append("\n\tNULL AS M_LDA_VALUE /*上月日均*/")
                        .append("\nFROM (\n\t");
            }else if (acctDate.length()==6){
                selectSql.append("\n\tCAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(54,18)) AS KPI_VALUE, /*当月值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1").append("`) AS DECIMAL(54,18)) AS M_LM_VALUE, /*上月值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(54,18)) AS Y_TY_VALUE, /*本年累计值（即本月值）*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0").append("`) AS DECIMAL(54,18)) AS M_LY_VALUE, /*上年同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0").append("`) AS DECIMAL(54,18)) AS Y_LY_VALUE, /*上年同期年累计值（即去年同期值）*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,EN").append("`) AS DECIMAL(54,18)) AS M_LY12_VALUE  /*上年12月值*/")
                        .append("\nFROM (\n\t");
            }
        }

        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成MC加工时自定义公式列表的最外层 select 语句
     * @Date 2023/4/1 21:07
     * @Param: [acctDate, kpiConfMap, etlConfMap, confProp]
     * @Return: java.lang.String
     */
    public static String  genMcBaseDefineSelectSql(String acctDate, HashMap<String, String> kpiConfMap,HashMap<String,HashMap<Integer,String>> etlConfMap,Properties confProp){
        String dateType = CommonFun.dateType(acctDate);
        StringBuilder selectSql = new StringBuilder();
        String kpiCode = kpiConfMap.get("KPI_CODE");
        long timeDistance = CommonFun.getTimeDistance(acctDate,"KPI_CODE,AVG,0,0,ST");
        selectSql.append("SELECT\n\t");
        if (CommonCheck.isRelationMap){
            CommonCheck.dstColList.stream().forEach(dstCol->{
                if (!dateType.equals(dstCol)&&!"KPI_CODE".equals(dstCol)){
                    selectSql.append(dstCol).append(",\n\t");
                }
            });
        }else {
            for (int i=0;i<etlConfMap.get("dstColMap").size();i++){
                if (!dateType.equals(etlConfMap.get("dstColMap").get(i))&&!"KPI_CODE".equals(etlConfMap.get("dstColMap").get(i))){
                    selectSql.append(etlConfMap.get("dstColMap").get(i)).append(",\n\t");
                }
            }
        }
//        selectSql.append("'").append(kpiConfMap.get("KPI_CODE")).append("'").append(" AS KPI_CODE,");
        if ("1".equals(kpiConfMap.get("IS_SUM"))){
            if (acctDate.length()==8){
                selectSql.append("CAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(38,18)) AS KPI_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,0,-1").append("`) AS DECIMAL(38,18)) AS D_LD_VALUE,")
                        .append("\n\tCAST(SUM(").append("NVL(`").append(kpiCode+",SUM,0,0,ST`,0)+NVL(`").append(kpiCode).append("`,0)) AS DECIMAL(38,18)) AS M_TM_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1,0").append("`) AS DECIMAL(38,18)) AS D_LM_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,0,-1,ST").append("`) AS DECIMAL(38,18)) AS M_LM_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0,0").append("`) AS DECIMAL(38,18)) AS D_LY_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,-1,0,ST").append("`) AS DECIMAL(38,18)) AS M_LY_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,0,0,ST`+`").append(kpiCode).append("`)/").append(timeDistance+1).append(" AS DECIMAL(38,18)) AS M_DA_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",AVG,0,-1,ST").append("`) AS DECIMAL(38,18)) AS M_LDA_VALUE \n")
                        .append("FROM (\n\t");
            }else if (acctDate.length()==6){
                selectSql.append("CAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(38,18)) AS KPI_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1").append("`) AS DECIMAL(38,18)) AS M_LM_VALUE,")
                        .append("\n\tCAST(SUM(").append("NVL(`").append(kpiCode+",SUM,0,ST`,0)+NVL(`").append(kpiCode).append("`,0)) AS DECIMAL(38,18)) AS Y_TY_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0").append("`) AS DECIMAL(38,18)) AS M_LY_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,-1,ST").append("`) AS DECIMAL(38,18)) AS Y_LY_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,EN").append("`) AS DECIMAL(38,18)) AS M_LY12_VALUE \n")
                        .append("FROM (\n\t");
            }
        }else {
            if (acctDate.length()==8){
                selectSql.append("CAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(38,18)) AS KPI_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,0,-1").append("`) AS DECIMAL(38,18)) AS D_LD_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(38,18)) AS M_TM_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1,0").append("`) AS DECIMAL(38,18)) AS D_LM_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1,0").append("`) AS DECIMAL(38,18)) AS M_LM_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0,0").append("`) AS DECIMAL(38,18)) AS D_LY_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0,0").append("`) AS DECIMAL(38,18)) AS M_LY_VALUE,")
                        .append("\n\tNULL AS M_DA_VALUE,")
                        .append("\n\tNULL AS M_LDA_VALUE \n")
                        .append("FROM (\n\t");
            }else if (acctDate.length()==6){
                selectSql.append("CAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(38,18)) AS KPI_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1").append("`) AS DECIMAL(38,18)) AS M_LM_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(38,18)) AS Y_TY_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0").append("`) AS DECIMAL(38,18)) AS M_LY_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0").append("`) AS DECIMAL(38,18)) AS Y_LY_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,EN").append("`) AS DECIMAL(38,18)) AS M_LY12_VALUE \n")
                        .append("FROM (\n\t");
            }
        }
        return selectSql.toString();
    }


    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成自定义公式列表的最外层 select 语句
     * @Date: 2022/11/25 12:39
     * @Param: [acctDate, kpiConfMap, etlConfMap, confProp]
     * @return: java.lang.String
     */
    public static String genBaseMc2AdbDefineSelectSql(String acctDate, HashMap<String, String> kpiConfMap, HashMap<String,HashMap<Integer,String>> etlConfMap, Properties confProp){
        StringBuilder selectSql = new StringBuilder();
        String kpiCode = kpiConfMap.get("KPI_CODE");
        selectSql.append("\nSELECT\n\t");
        if (CommonCheck.isRelationMap){
            CommonCheck.dstColList.stream().forEach(dstCol->{
                selectSql.append(dstCol).append(",\n\t");
            });
        }else {
            for (int i=0;i<etlConfMap.get("dstColMap").size();i++){
                selectSql.append(etlConfMap.get("dstColMap").get(i)).append(",\n\t");
            }
        }
        selectSql.append("'").append(kpiConfMap.get("KPI_CODE")).append("'").append(" AS KPI_CODE,");
        if ("1".equals(kpiConfMap.get("IS_SUM"))){
            if (acctDate.length()==8){
                selectSql.append("\n\tCAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(54,18)) AS KPI_VALUE,/*当日值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,0,-1").append("`) AS DECIMAL(54,18)) AS D_LD_VALUE, /*昨日值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,0,0,ST").append("`) AS DECIMAL(54,18)) AS M_TM_VALUE, /*本月累计值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1,0").append("`) AS DECIMAL(54,18)) AS D_LM_VALUE, /*上月同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,0,-1,ST").append("`) AS DECIMAL(54,18)) AS M_LM_VALUE, /*上月同期累计值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0,0").append("`) AS DECIMAL(54,18)) AS D_LY_VALUE, /*去年同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,-1,0,ST").append("`) AS DECIMAL(54,18)) AS M_LY_VALUE, /*去年同期累计值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",AVG,0,0,ST").append("`) AS DECIMAL(54,18)) AS M_DA_VALUE, /*本月日均*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",AVG,0,-1,ST").append("`) AS DECIMAL(54,18)) AS M_LDA_VALUE /*上月日均*/")
                        .append("\nFROM (\n\t");
            }else if (acctDate.length()==6){
                selectSql.append("\n\tCAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(54,18)) AS KPI_VALUE, /*当月值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1").append("`) AS DECIMAL(54,18)) AS M_LM_VALUE, /*上月值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,0,ST").append("`) AS DECIMAL(54,18)) AS Y_TY_VALUE, /*本年累计值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0").append("`) AS DECIMAL(54,18)) AS M_LY_VALUE, /*上年同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,-1,ST").append("`) AS DECIMAL(54,18)) AS Y_LY_VALUE, /*上年同期年累计值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,EN").append("`) AS DECIMAL(54,18)) AS M_LY12_VALUE  /*上年12月值*/")
                        .append("\nFROM (\n\t");
            }
        }else {
            if (acctDate.length()==8){
                selectSql.append("\n\tCAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(54,18)) AS KPI_VALUE,/*当日值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,0,-1").append("`) AS DECIMAL(54,18)) AS D_LD_VALUE, /*昨日值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(54,18)) AS M_TM_VALUE, /*本月累计值(即本期值）*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1,0").append("`) AS DECIMAL(54,18)) AS D_LM_VALUE, /*上月同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1,0").append("`) AS DECIMAL(54,18)) AS M_LM_VALUE, /*上月同期累计值（即上月同期值）*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0,0").append("`) AS DECIMAL(54,18)) AS D_LY_VALUE, /*去年同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0,0").append("`) AS DECIMAL(54,18)) AS M_LY_VALUE, /*去年同期累计值（即去年同期值）*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",AVG,0,0,ST").append("`) AS DECIMAL(54,18)) AS M_DA_VALUE, /*本月日均*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",AVG,0,-1,ST").append("`) AS DECIMAL(54,18)) AS M_LDA_VALUE /*上月日均*/")
                        .append("\nFROM (\n\t");
            }else if (acctDate.length()==6){
                selectSql.append("\n\tCAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(54,18)) AS KPI_VALUE, /*当月值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1").append("`) AS DECIMAL(54,18)) AS M_LM_VALUE, /*上月值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode).append("`").append(") AS DECIMAL(54,18)) AS Y_TY_VALUE, /*本年累计值（即本月值）*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0").append("`) AS DECIMAL(54,18)) AS M_LY_VALUE, /*上年同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0").append("`) AS DECIMAL(54,18)) AS Y_LY_VALUE, /*上年同期年累计值（即去年同期值）*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,EN").append("`) AS DECIMAL(54,18)) AS M_LY12_VALUE  /*上年12月值*/")
                        .append("\nFROM (\n\t");
            }
        }

        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成来源表和目标表字段关系一一对应加工的 select 语句
     * @Date: 2023/02/03 15:39
     * @Param: [acctDate, kpiConfMap, etlConfMap, confProp]
     * @return: java.lang.String
     */
    public static String  genBaseMapSelectSql(String acctDate, HashMap<String, String> kpiConfMap,HashMap<String,HashMap<Integer,String>> etlConfMap,Properties confProp){
        StringBuilder selectSql = new StringBuilder();
        String kpiCode = kpiConfMap.get("KPI_CODE");
        selectSql.append("\nSELECT\n\t");
        if (CommonCheck.isRelationMap){
            CommonCheck.dstColList.stream().forEach(dstCol->{
                selectSql.append(dstCol).append(",\n\t");
            });
        }else {
            for (int i=0;i<etlConfMap.get("srcColMap").size();i++){
                selectSql.append(etlConfMap.get("srcColMap").get(i)).append(",\n\t");
            }
        }
        selectSql.append("'").append(kpiConfMap.get("KPI_CODE")).append("'").append(" AS KPI_CODE,");
        if (acctDate.length()==8){
            selectSql.append("\n\tCAST(SUM(KPI_VALUE) AS DECIMAL(54,18)) AS KPI_VALUE,/*当日值*/")
                    .append("\n\tCAST(SUM(D_LD_VALUE) AS DECIMAL(54,18)) AS D_LD_VALUE, /*昨日值*/")
                    .append("\n\tCAST(SUM(M_TM_VALUE) AS DECIMAL(54,18)) AS M_TM_VALUE, /*本月累计值*/")
                    .append("\n\tCAST(SUM(D_LM_VALUE) AS DECIMAL(54,18)) AS D_LM_VALUE, /*上月同期值*/")
                    .append("\n\tCAST(SUM(M_LM_VALUE) AS DECIMAL(54,18)) AS M_LM_VALUE, /*上月同期累计值*/")
                    .append("\n\tCAST(SUM(D_LY_VALUE) AS DECIMAL(54,18)) AS D_LY_VALUE, /*去年同期值*/")
                    .append("\n\tCAST(SUM(M_LY_VALUE) AS DECIMAL(54,18)) AS M_LY_VALUE, /*去年同期累计值*/")
                    .append("\n\tCAST(SUM(M_DA_VALUE) AS DECIMAL(54,18)) AS M_DA_VALUE, /*本月日均*/")
                    .append("\n\tCAST(SUM(M_LDA_VALUE) AS DECIMAL(54,18)) AS M_LDA_VALUE /*上月日均*/")
                    .append("\nFROM (\n\t");
        }else if (acctDate.length()==6){
            selectSql.append("\n\tCAST(SUM(KPI_VALUE) AS DECIMAL(54,18)) AS KPI_VALUE, /*当月值*/")
                    .append("\n\tCAST(SUM(M_LM_VALUE) AS DECIMAL(54,18)) AS M_LM_VALUE, /*上月值*/")
                    .append("\n\tCAST(SUM(Y_TY_VALUE) AS DECIMAL(54,18)) AS Y_TY_VALUE, /*本年累计值*/")
                    .append("\n\tCAST(SUM(M_LY_VALUE) AS DECIMAL(54,18)) AS M_LY_VALUE, /*上年同期值*/")
                    .append("\n\tCAST(SUM(Y_LY_VALUE) AS DECIMAL(54,18)) AS Y_LY_VALUE, /*上年同期年累计值*/")
                    .append("\n\tNULL AS M_LY12_VALUE  /*上年12月值*/")
                    .append("\nFROM " + kpiConfMap.get("SRC_ENTITY_NAME"))
                    .append(" ");
        }
        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: Mc上生成来源表和目标表字段关系一一对应加工的 select 语句
     * @Date: 2023/02/03 15:39
     * @Param: [acctDate, kpiConfMap, etlConfMap, confProp]
     * @return: java.lang.String
     */
    public static String  genMcBaseMapSelectSql(String acctDate, HashMap<String, String> kpiConfMap,HashMap<String,HashMap<Integer,String>> etlConfMap,Properties confProp){
        String dateType = CommonFun.dateType(acctDate);
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("SELECT\n\t");
        if (CommonCheck.isRelationMap){
            CommonCheck.dstColList.stream().forEach(dstCol->{
                if (!dateType.equals(dstCol)){
                    selectSql.append(dstCol).append(",\n\t");
                }
            });
        }else {
            for (int i=0;i<etlConfMap.get("srcColMap").size();i++){
                if (!dateType.equals(etlConfMap.get("srcColMap").get(i))){
                    selectSql.append(etlConfMap.get("srcColMap").get(i)).append(",\n\t");
                }
            }
        }
        if (acctDate.length()==8){
            selectSql.append("CAST(SUM(KPI_VALUE) AS DECIMAL(38,18)) AS KPI_VALUE,")
                    .append("\n\tCAST(SUM(D_LD_VALUE) AS DECIMAL(38,18)) AS D_LD_VALUE,")
                    .append("\n\tCAST(SUM(M_TM_VALUE) AS DECIMAL(38,18)) AS M_TM_VALUE,")
                    .append("\n\tCAST(SUM(D_LM_VALUE) AS DECIMAL(38,18)) AS D_LM_VALUE,")
                    .append("\n\tCAST(SUM(M_LM_VALUE) AS DECIMAL(38,18)) AS M_LM_VALUE,")
                    .append("\n\tCAST(SUM(D_LY_VALUE) AS DECIMAL(38,18)) AS D_LY_VALUE,")
                    .append("\n\tCAST(SUM(M_LY_VALUE) AS DECIMAL(38,18)) AS M_LY_VALUE,")
                    .append("\n\tCAST(SUM(M_DA_VALUE) AS DECIMAL(38,18)) AS M_DA_VALUE,")
                    .append("\n\tCAST(SUM(M_LDA_VALUE) AS DECIMAL(38,18)) AS M_LDA_VALUE ")
                    .append("\nFROM (\n\t");
        }else if (acctDate.length()==6){
            selectSql.append("CAST(SUM(KPI_VALUE) AS DECIMAL(38,18)) AS KPI_VALUE,")
                    .append("\n\tCAST(SUM(M_LM_VALUE) AS DECIMAL(38,18)) AS M_LM_VALUE,")
                    .append("\n\tCAST(SUM(Y_TY_VALUE) AS DECIMAL(38,18)) AS Y_TY_VALUE,")
                    .append("\n\tCAST(SUM(M_LY_VALUE) AS DECIMAL(38,18)) AS M_LY_VALUE,")
                    .append("\n\tCAST(SUM(Y_LY_VALUE) AS DECIMAL(38,18)) AS Y_LY_VALUE,")
                    .append("\n\tNULL AS M_LY12_VALUE ")
                    .append("\nFROM ITSY_CUBE." + kpiConfMap.get("SRC_ENTITY_NAME")).append(" ");
        }
        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成自定义公式列表的最外层 select 语句
     * @Date: 2022/11/25 12:39
     * @Param: [acctDate, kpiConfMap, etlConfMap, confProp]
     * @return: java.lang.String
     */
    public static String  genJSDefineSelectSql(String acctDate, HashMap<String, String> kpiConfMap,HashMap<Integer,String> jsEtlConf){
        long timeDistance = CommonFun.getTimeDistance(acctDate,"KPI_CODE,AVG,0,0,ST");
        StringBuilder selectSql = new StringBuilder();
        String kpiCode = kpiConfMap.get("KPI_CODE");
        selectSql.append("\nSELECT\n\t");
        if (CommonCheck.isRelationMap){
            CommonCheck.dstColList.stream().forEach(dstCol->{
                selectSql.append(dstCol).append(",\n\t");
            });
        }else {
            for (int i=0;i<jsEtlConf.size();i++){
                selectSql.append(jsEtlConf.get(i)).append(",\n\t");
            }
        }
        selectSql.append("'").append(kpiConfMap.get("KPI_CODE")).append("'").append(" AS KPI_CODE,");
        if ("1".equals(kpiConfMap.get("IS_SUM"))){
            if (acctDate.length()==8){
                selectSql.append("\n\tCAST(SUM(").append(kpiConfMap.get(FORMULATION).replaceAll("[\\{\\}]","`")).append(") AS DECIMAL(54,18)) AS KPI_VALUE, /*当日值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,0,-1").append("`) AS DECIMAL(54,18)) AS D_LD_VALUE, /*昨日值*/")
                        .append("\n\tCAST(SUM(").append("NVL(`").append(kpiCode+",SUM,0,0,ST`,0)+NVL(").append(kpiConfMap.get(FORMULATION).replaceAll("[\\{\\}]","`")).append(",0)) AS DECIMAL(54,18)) AS M_TM_VALUE, /*本月累计值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1,0").append("`) AS DECIMAL(54,18)) AS D_LM_VALUE, /*上月同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,0,-1,ST").append("`) AS DECIMAL(54,18)) AS M_LM_VALUE, /*上月同期累计值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0,0").append("`) AS DECIMAL(54,18)) AS D_LY_VALUE, /*去年同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,-1,0,ST").append("`) AS DECIMAL(54,18)) AS M_LY_VALUE, /*去年同期累计值*/")
                        .append("\n\tCAST(SUM(").append("NVL(`").append(kpiCode+",SUM,0,0,ST`,0)+NVL(").append(kpiConfMap.get(FORMULATION).replaceAll("[\\{\\}]","`")).append(",0))/").append(timeDistance+1).append(" AS DECIMAL(54,18)) AS M_DA_VALUE, /*本月日均*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",AVG,0,-1,ST").append("`) AS DECIMAL(54,18)) AS M_LDA_VALUE /*上月日均*/")
                        .append("\nFROM (\n\t");
            }else if (acctDate.length()==6){
                selectSql.append("\n\tCAST(SUM(").append(kpiConfMap.get(FORMULATION).replaceAll("[\\{\\}]","`")).append(") AS DECIMAL(54,18)) AS KPI_VALUE, /*当月值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1").append("`) AS DECIMAL(54,18)) AS M_LM_VALUE, /*上月值*/")
                        .append("\n\tCAST(SUM(").append("NVL(`").append(kpiCode+",SUM,0,ST`,0)+NVL(").append(kpiConfMap.get(FORMULATION).replaceAll("[\\{\\}]","`")).append(",0)) AS DECIMAL(54,18)) AS Y_TY_VALUE, /*本年累计值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0").append("`) AS DECIMAL(54,18)) AS M_LY_VALUE, /*上年同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,-1,ST").append("`) AS DECIMAL(54,18)) AS Y_LY_VALUE, /*上年同期年累计值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,EN").append("`) AS DECIMAL(54,18)) AS M_LY12_VALUE  /*上年12月值*/")
                        .append("\nFROM (\n\t");
            }
        }else {
            if (acctDate.length()==8){
                selectSql.append("\n\tCAST(SUM(").append(kpiConfMap.get(FORMULATION).replaceAll("[\\{\\}]","`")).append(") AS DECIMAL(54,18)) AS KPI_VALUE, /*当日值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,0,-1").append("`) AS DECIMAL(54,18)) AS D_LD_VALUE, /*昨日值*/")
                        .append("\n\tCAST(SUM(").append(kpiConfMap.get(FORMULATION).replaceAll("[\\{\\}]","`")).append(") AS DECIMAL(54,18)) AS KPI_VALUE, /*本月累计值(即本期值）*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1,0").append("`) AS DECIMAL(54,18)) AS D_LM_VALUE, /*上月同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1,0").append("`) AS DECIMAL(54,18)) AS M_LM_VALUE, /*上月同期累计值（即上月同期值）*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0,0").append("`) AS DECIMAL(54,18)) AS D_LY_VALUE, /*去年同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0,0").append("`) AS DECIMAL(54,18)) AS M_LY_VALUE, /*去年同期累计值（即去年同期值）*/")
                        .append("\n\tNULL AS M_DA_VALUE, /*本月日均*/")
                        .append("\n\tNULL AS M_LDA_VALUE /*上月日均*/")
                        .append("\nFROM (\n\t");
            }else if (acctDate.length()==6){
                selectSql.append("\n\tCAST(SUM(").append(kpiConfMap.get(FORMULATION).replaceAll("[\\{\\}]","`")).append(") AS DECIMAL(54,18)) AS KPI_VALUE, /*当日值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1").append("`) AS DECIMAL(54,18)) AS M_LM_VALUE, /*上月值*/")
                        .append("\n\tCAST(SUM(").append(kpiConfMap.get(FORMULATION).replaceAll("[\\{\\}]","`")).append(") AS DECIMAL(54,18)) AS KPI_VALUE, /*本年累计值（即本月值）*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0").append("`) AS DECIMAL(54,18)) AS M_LY_VALUE, /*上年同期值*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0").append("`) AS DECIMAL(54,18)) AS Y_LY_VALUE, /*上年同期年累计值（即去年同期值）*/")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,EN").append("`) AS DECIMAL(54,18)) AS M_LY12_VALUE  /*上年12月值*/")
                        .append("\nFROM (\n\t");
            }
        }
        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成自定义公式列表的最外层 select 语句
     * @Date: 2022/11/25 12:39
     * @Param: [acctDate, kpiConfMap, etlConfMap, confProp]
     * @return: java.lang.String
     */
    public static String  genMcJSDefineSelectSql(String acctDate, HashMap<String, String> kpiConfMap,HashMap<Integer,String> jsEtlConf){
        String dateType = CommonFun.dateType(acctDate);
        long timeDistance = CommonFun.getTimeDistance(acctDate,"KPI_CODE,AVG,0,0,ST");
        StringBuilder selectSql = new StringBuilder();
        String kpiCode = kpiConfMap.get("KPI_CODE");
        selectSql.append("SELECT\n\t");
        if (CommonCheck.isRelationMap){
            CommonCheck.dstColList.stream().forEach(dstCol->{
                if (!dateType.equals(dstCol)){
                    selectSql.append(dstCol).append(",\n\t");
                }
            });
        }else {
            for (int i=0;i<jsEtlConf.size();i++){
                if (!dateType.equals(jsEtlConf.get(i))){
                    selectSql.append(jsEtlConf.get(i)).append(",\n\t");
                }
            }
        }
        if ("1".equals(kpiConfMap.get("IS_SUM"))){
            if (acctDate.length()==8){
                selectSql.append("CAST(SUM(").append(CommonFun.convertMcDecode(kpiConfMap.get(FORMULATION)).replaceAll("[\\{\\}]","`")).append(") AS DECIMAL(38,18)) AS KPI_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,0,-1").append("`) AS DECIMAL(38,18)) AS D_LD_VALUE,")
                        .append("\n\tCAST(SUM(").append("NVL(`").append(kpiCode+",SUM,0,0,ST`,0)+NVL(").append(CommonFun.convertMcDecode(kpiConfMap.get(FORMULATION)).replaceAll("[\\{\\}]","`")).append(",0)) AS DECIMAL(38,18)) AS M_TM_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1,0").append("`) AS DECIMAL(38,18)) AS D_LM_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,0,-1,ST").append("`) AS DECIMAL(38,18)) AS M_LM_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0,0").append("`) AS DECIMAL(38,18)) AS D_LY_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,-1,0,ST").append("`) AS DECIMAL(38,18)) AS M_LY_VALUE,")
                        .append("\n\tCAST(SUM(").append("NVL(`").append(kpiCode+",SUM,0,0,ST`,0)+NVL(").append(CommonFun.convertMcDecode(kpiConfMap.get(FORMULATION)).replaceAll("[\\{\\}]","`")).append(",0))/").append(timeDistance+1).append(" AS DECIMAL(38,18)) AS M_DA_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",AVG,0,-1,ST").append("`) AS DECIMAL(38,18)) AS M_LDA_VALUE \n")
                        .append("FROM (\n\t");
            }else if (acctDate.length()==6){
                selectSql.append("CAST(SUM(").append(CommonFun.convertMcDecode(kpiConfMap.get(FORMULATION)).replaceAll("[\\{\\}]","`")).append(") AS DECIMAL(38,18)) AS KPI_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1").append("`) AS DECIMAL(38,18)) AS M_LM_VALUE,")
                        .append("\n\tCAST(SUM(").append("NVL(`").append(kpiCode+",SUM,0,ST`,0)+NVL(").append(CommonFun.convertMcDecode(kpiConfMap.get(FORMULATION)).replaceAll("[\\{\\}]","`")).append(",0)) AS DECIMAL(38,18)) AS Y_TY_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0").append("`) AS DECIMAL(38,18)) AS M_LY_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",SUM,-1,ST").append("`) AS DECIMAL(38,18)) AS Y_LY_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,EN").append("`) AS DECIMAL(38,18)) AS M_LY12_VALUE \n")
                        .append("FROM (\n\t");
            }
        }else {
            if (acctDate.length()==8){
                selectSql.append("CAST(SUM(").append(CommonFun.convertMcDecode(kpiConfMap.get(FORMULATION)).replaceAll("[\\{\\}]","`")).append(") AS DECIMAL(38,18)) AS KPI_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,0,-1").append("`) AS DECIMAL(38,18)) AS D_LD_VALUE,")
                        .append("\n\tCAST(SUM(").append(CommonFun.convertMcDecode(kpiConfMap.get(FORMULATION)).replaceAll("[\\{\\}]","`")).append(") AS DECIMAL(38,18)) AS KPI_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1,0").append("`) AS DECIMAL(38,18)) AS D_LM_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1,0").append("`) AS DECIMAL(38,18)) AS M_LM_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0,0").append("`) AS DECIMAL(38,18)) AS D_LY_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0,0").append("`) AS DECIMAL(38,18)) AS M_LY_VALUE,")
                        .append("\n\tNULL AS M_DA_VALUE,")
                        .append("\n\tNULL AS M_LDA_VALUE \n")
                        .append("FROM (\n\t");
            }else if (acctDate.length()==6){
                selectSql.append("CAST(SUM(").append(CommonFun.convertMcDecode(kpiConfMap.get(FORMULATION)).replaceAll("[\\{\\}]","`")).append(") AS DECIMAL(38,18)) AS KPI_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,0,-1").append("`) AS DECIMAL(38,18)) AS M_LM_VALUE,")
                        .append("\n\tCAST(SUM(").append(CommonFun.convertMcDecode(kpiConfMap.get(FORMULATION)).replaceAll("[\\{\\}]","`")).append(") AS DECIMAL(38,18)) AS Y_TY_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0").append("`) AS DECIMAL(38,18)) AS M_LY_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,0").append("`) AS DECIMAL(38,18)) AS Y_LY_VALUE,")
                        .append("\n\tCAST(SUM(").append("`").append(kpiCode+",NOR,-1,EN").append("`) AS DECIMAL(38,18)) AS M_LY12_VALUE \n")
                        .append("FROM (\n\t");
            }
        }
        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据配置表生成基础指标 select 语句部分 (带自定义公式列表）
     * @Date: 2022/11/22 19:46
     * @Param: [kpiConfMap, srcColMap]
     * @return: java.lang.String
     */
    public static String genBaseDefineSelectSQL(String acctDate,HashMap<String,String> kpiConfMap,HashMap<Integer,String> srcColMap){
        String srcTable = kpiConfMap.get("SRC_ENTITY_NAME");
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("\nSELECT\n\t");
        for(int i=0;i<srcColMap.size();i++){
            selectSql.append(srcColMap.get(i)).append(",\n\t");
        }
        if (acctDate.length()==8){
            selectSql.append("KPI_CODE,\n\t")
                    .append("KPI_VALUE,\n\t")
                    .append("D_LD_VALUE,\n\t")
                    .append("M_TM_VALUE,\n\t")
                    .append("D_LM_VALUE,\n\t")
                    .append("M_LM_VALUE,\n\t")
                    .append("D_LY_VALUE,\n\t")
                    .append("M_LY_VALUE,\n\t")
                    .append("M_DA_VALUE,\n\t")
                    .append("M_LDA_VALUE\n)");
        }else if (acctDate.length()==6){
            selectSql.append("KPI_CODE,\n\t")
                    .append("KPI_VALUE,\n\t")
                    .append("M_LM_VALUE,\n\t")
                    .append("Y_TY_VALUE,\n\t")
                    .append("M_LY_VALUE,\n\t")
                    .append("Y_LY_VALUE,\n\t")
                    .append("M_LY12_VALUE\n)");
        }
        selectSql.append("'"+kpiConfMap.get("KPI_CODE")+"' AS KPI_CODE,")
                .append("\n\tCAST(SUM("+kpiConfMap.get("SRC_VALUE_COL")+") AS DECIMAL(54,18)) AS KPI_VALUE")
                .append("\nFROM ").append(srcTable);
        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成基础指标 限定条件部分
     * @Date: 2022/11/20 21:05
     * @Param: [acctDate, kpiConfMap]
     * @return: java.lang.String
     */
    public static String genBaseWhereSql(String acctDate,HashMap<String,String> kpiConfMap){
        String dateType = CommonFun.dateType(acctDate);
        StringBuilder whereConditionSql = new StringBuilder();
        if (acctDate.length()==6&&(kpiConfMap.get("SRC_ENTITY_NAME").contains("_D_")||kpiConfMap.get("SRC_ENTITY_NAME").endsWith("_D"))){
            if ("取Cube上月最后一天".equals(kpiConfMap.get("KPI_EXPLAIN"))){
                whereConditionSql.append("\nWHERE DATE_ID = '").append(CommonFun.getLastDayOfPreviousMonth(acctDate)).append("'");
            }else {
                whereConditionSql.append("\nWHERE DATE_ID LIKE '").append(acctDate).append("%'");
            }
        }else {
            whereConditionSql.append("\nWHERE ").append(dateType).append(" = '").append(acctDate).append("'");
        }
        String colBase = "COL_OPER";
        String colName = null;
        for(int i=1;i<10;i++){
            colName = colBase+String.valueOf(i);
            if (i==1){
                if (String.valueOf(kpiConfMap.get(colName)).isEmpty()||kpiConfMap.get(colName)==null){
                    //若第一列无配置
                }else {
                    whereConditionSql.append("\n\tAND ").append(kpiConfMap.get(colName));
                }
            }else{
                if(!StringUtils.isNullOrEmpty(kpiConfMap.get(colName))){
                    whereConditionSql.append("\n\t").append(kpiConfMap.get(colName));
                }
            }
        }
        return whereConditionSql.toString()+" ";
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成基础指标 限定条件部分
     * @Date: 2022/11/20 21:05
     * @Param: [acctDate, kpiConfMap]
     * @return: java.lang.String
     */
    public static String genBaseMcWhereSql(String acctDate,HashMap<String,String> kpiConfMap){
        String dateType = CommonFun.dateType(acctDate);
        StringBuilder whereConditionSql = new StringBuilder();
        whereConditionSql.append("\nWHERE ").append(dateType).append(" = '").append(acctDate).append("'")
                .append("\n\tAND KPI_CODE = '").append(kpiConfMap.get("KPI_CODE")).append("'")
                .append("\n\tAND AREA_ID != '-1'");
        return whereConditionSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成基础指标 限定条件部分
     * @Date: 2022/11/20 21:05
     * @Param: [acctDate, kpiConfMap]
     * @return: java.lang.String
     */
    public static String genMcBaseWhereSql(String acctDate,HashMap<String,String> kpiConfMap){
        String dateType = CommonFun.dateType(acctDate);
        StringBuilder whereConditionSql = new StringBuilder();
        if (acctDate.length()==6&&(kpiConfMap.get("SRC_ENTITY_NAME").contains("_D_")||kpiConfMap.get("SRC_ENTITY_NAME").endsWith("_D"))){
            if ("取Cube上月最后一天".equals(kpiConfMap.get("KPI_EXPLAIN"))){
                whereConditionSql.append("\nWHERE DATE_ID = '").append(CommonFun.getLastDayOfPreviousMonth(acctDate)).append("'");
            }else {
                whereConditionSql.append("\nWHERE DATE_ID LIKE '").append(acctDate).append("%'");
            }
        }else {
            whereConditionSql.append("\nWHERE ").append(dateType).append(" = '").append(acctDate).append("'");
        }
        String colBase = "COL_OPER";
        String colName = null;
        for(int i=1;i<10;i++){
            colName = colBase+String.valueOf(i);
            if (i==1){
                if (String.valueOf(kpiConfMap.get(colName)).isEmpty()||kpiConfMap.get(colName)==null){
                    //若第一列无配置
                }else {
                    whereConditionSql.append("\n\tAND ").append(kpiConfMap.get(colName));
                }
            }else{
                if(!StringUtils.isNullOrEmpty(kpiConfMap.get(colName))){
                    whereConditionSql.append("\n\t").append(kpiConfMap.get(colName));
                }
            }
        }
        return whereConditionSql.toString()+" ";
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 通过 ETL来源表的配置，实现通用的生成group by部分方法
     * @Date: 2022/11/22 19:13
     * @Param: [srcColMap]
     * @return: java.lang.String
     */
    public static String genGroupBySql(HashMap<Integer,String> srcColMap){
        StringBuilder groupBySql = new StringBuilder();
        groupBySql.append("\nGROUP BY\n\t");
        if (CommonCheck.isRelationMap){
            for(int i=0;i<CommonCheck.dstColList.size();i++){
                if (i<CommonCheck.dstColList.size()-1){
                    groupBySql.append(CommonCheck.dstColList.get(i)).append(",\n\t");
                }else {
                    groupBySql.append(CommonCheck.dstColList.get(i));
                }
            }
        }else {
            for(int i=0;i<srcColMap.size();i++){
                if (i<srcColMap.size()-1){
                    groupBySql.append(srcColMap.get(i)).append(",\n\t");
                }else {
                    groupBySql.append(srcColMap.get(i));
                }
            }
        }
        return groupBySql.toString()+"\n";
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 通过 ETL来源表的配置，实现通用的生成group by部分方法
     * @Date: 2022/11/22 19:13
     * @Param: [srcColMap]
     * @return: java.lang.String
     */
    public static String genMcGroupBySql(String acctDate,HashMap<Integer,String> srcColMap){
        StringBuilder groupBySql = new StringBuilder();
        groupBySql.append("\nGROUP BY\n\t");
        srcColMap = CommonFun.removeDuplicateValuesFromHashMap(srcColMap);
        for(int i=0;i<srcColMap.size();i++) {
            if (i < srcColMap.size()-1) {
                if ("DATE_ID".equals(srcColMap.get(i+1))) {
                    groupBySql.append("'").append(acctDate).append("',\n\t");
                } else if ("MONTH_ID".equals(srcColMap.get(i+1))) {
                    groupBySql.append("'").append(acctDate.substring(0, 6)).append("',\n\t");
                } else if ("DAY_ID".equals(srcColMap.get(i+1))) {
                    groupBySql.append("'").append(acctDate.substring(6, 8)).append("',\n\t");
                } else {
                    groupBySql.append(srcColMap.get(i+1)).append(",\n\t");
                }
            } else {
                if ("DATE_ID".equals(srcColMap.get(i+1))) {
                    groupBySql.append("'").append(acctDate).append("'");
                } else if ("MONTH_ID".equals(srcColMap.get(i+1))) {
                    groupBySql.append("'").append(acctDate.substring(0, 6)).append("'");
                } else if ("DAY_ID".equals(srcColMap.get(i+1))) {
                    groupBySql.append("'").append(acctDate.substring(6, 8)).append("'");
                } else {
                    groupBySql.append(srcColMap.get(i+1));
                }
            }
        }
        return groupBySql.toString()+"\t";
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 通过全局变量获取的目标表列信息，获取通用的Group by方法
     * @Date: 2022/11/22 19:13
     * @Param: [srcColMap]
     * @return: java.lang.String
     */
    public static String genGroupBySql(List<String> dstColList){
        StringBuilder groupBySql = new StringBuilder();
        groupBySql.append("\nGROUP BY\n\t");
        dstColList.stream().limit(dstColList.size()-1).forEach(dstCol->{
            if (!"DATE_ID".equals(dstCol)&&!"MONTH_ID".equals(dstCol)&&!"DAY_ID".equals(dstCol)){
                groupBySql.append(dstCol).append(",\n\t");
            }
        });
        dstColList.stream().skip(dstColList.size()-1).findFirst().ifPresent(dstCol-> {
            if ("DATE_ID".equals(dstCol)||"MONTH_ID".equals(dstCol)||"DAY_ID".equals(dstCol)){
                groupBySql.append("NULL");
            }else {
                groupBySql.append(dstCol);
            }
        });
        return groupBySql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 通过全局变量获取的目标表列信息，获取通用的Group by方法
     * @Date: 2022/11/22 19:13
     * @Param: [srcColMap]
     * @return: java.lang.String
     */
    public static String genMcGroupBySql(String acctDate,List<String> dstColList){
        StringBuilder groupBySql = new StringBuilder();
        groupBySql.append("\nGROUP BY\n\t");
        dstColList.stream().limit(dstColList.size()-1).forEach(dstCol->{
            if ("DATE_ID".equals(dstCol)){
                groupBySql.append("'").append(acctDate).append("',\n\t");
            }else if ("MONTH_ID".equals(dstCol)){
                groupBySql.append("'").append(acctDate.substring(0,6)).append("',\n\t");
            }else if ("DAY_ID".equals(dstCol)){
                groupBySql.append("'").append(acctDate.substring(6,8)).append("',\n\t");
            }else {
                groupBySql.append(dstCol).append(",\n\t");
            }
        });
        dstColList.stream().skip(dstColList.size()-1).findFirst().ifPresent(dstCol-> {
            if ("DATE_ID".equals(dstCol)){
                groupBySql.append("'").append(acctDate).append("'");
            }else if ("MONTH_ID".equals(dstCol)){
                groupBySql.append("'").append(acctDate.substring(0,6)).append("'");
            }else if ("DAY_ID".equals(dstCol)){
                groupBySql.append("'").append(acctDate.substring(6,8)).append("'");
            }else {
                groupBySql.append(dstCol);
            }
        });
        return groupBySql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 通过全局变量获取的目标表列信息，获取通用的Group by方法
     * @Date: 2022/11/22 19:13
     * @Param: [srcColMap]
     * @return: java.lang.String
     */
    public static String genJSUnionGroupBySql(String acctDate,List<String> dstColList){
        StringBuilder groupBySql = new StringBuilder();
        List<Object> list = new ArrayList<>();
        list.add("DATE_ID");
        list.add("MONTH_ID");
        list.add("DAY_ID");
        groupBySql.append("\nGROUP BY\n\t");
        dstColList.stream().forEach(dstCol->{
            if ("DATE_ID".equals(dstCol)){
                groupBySql.append("'").append(acctDate).append("',\n\t");
            }else if ("MONTH_ID".equals(dstCol)){
                groupBySql.append("'").append(acctDate.substring(0,6)).append("',\n\t");
            }else if ("DAY_ID".equals(dstCol)){
                groupBySql.append("'").append(acctDate.substring(6,8)).append("',\n\t");
            }else {
                groupBySql.append(dstCol).append(",\n\t");
            }
        });
        return groupBySql.toString().substring(0,groupBySql.toString().length()-3);
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 通过全局变量获取的目标表列信息，获取通用的Group by方法
     * @Date: 2022/11/22 19:13
     * @Param: [srcColMap]
     * @return: java.lang.String
     */
    public static String genGroupBySql(String formular,HashMap<String,Object> rmEtlConfMap){
        StringBuilder groupBySql = new StringBuilder();
        groupBySql.append("\nGROUP BY\n\t");
        Object etlConfObject = rmEtlConfMap.get(CommonFun.getSplitByIndex(formular, 0));
        List<String> list = new ArrayList<>();
        list.add("DATE_ID");
        list.add("MONTH_ID");
        list.add("DAY_ID");
        if (etlConfObject instanceof List){
            ((List) etlConfObject).stream().limit(((List) etlConfObject).size()-1).forEach(dstCol->{
                if (!list.contains(dstCol)){
                    groupBySql.append(dstCol).append(",\n\t");
                }
            });
            ((List) etlConfObject).stream().skip(((List) etlConfObject).size()-1).findFirst().ifPresent(dstCol->{
                if (!list.contains(dstCol)){
                    groupBySql.append(dstCol);
                }
            });
        }else if (etlConfObject instanceof HashMap){
            ((HashMap) etlConfObject).keySet().stream().limit(((HashMap) etlConfObject).size()-1).forEach(key->{
                if (!list.contains(((HashMap) etlConfObject).get(key))){
                    groupBySql.append(((HashMap) etlConfObject).get(key)).append(",\n\t");
                }
            });
            ((HashMap) etlConfObject).keySet().stream().skip(((HashMap) etlConfObject).size()-1).findFirst().ifPresent(key->{
                if (!list.contains(((HashMap) etlConfObject).get(key))){
                    groupBySql.append(((HashMap) etlConfObject).get(key));
                }
            });

        }
        return groupBySql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成计算指标union all部分来源表是基础指标的select语句
     * @Date: 2022/11/20 18:34
     * @Param: []
     * @return: java.lang.String
     */
    public static String genJSUnionBaseSelectSQL(String acctDate,String formular, HashMap<Integer,String> srcColMap, String srcTable ){
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("\nSELECT\n\t");
        for(int i=0;i<srcColMap.size();i++){
            selectSql.append(srcColMap.get(i)).append(",\n\t");
        }
        selectSql.append("'").append(formular).append("' AS KPI_CODE,")
                .append(genJSMathSql(acctDate,formular))
                .append("\nFROM ").append(srcTable);
        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成计算指标union all部分来源指标是计算指标的select语句
     * @Date: 2022/11/20 18:34
     * @Param: []
     * @return: java.lang.String
     */
    public static String genJSUnionJSSelectSQL(String acctDate,String formular,HashMap<String,String> kpiConfMap,HashMap<Integer,String> jsEtlConf,Connection conn){
        String dstTable = kpiConfMap.get("DST_ENTITY_NAME");
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("\nSELECT\n\t");
        CommonCheck.checkRelationMap(kpiConfMap,conn);
        if (CommonCheck.isRelationMap){
            CommonCheck.dstColList.stream().forEach(dstCol->
                    selectSql.append(dstCol).append(",\n\t"));
        }else {
            for(int i=0;i<jsEtlConf.size();i++){
                selectSql.append(jsEtlConf.get(i)).append(",\n\t");
            }
        }
        selectSql.append("'").append(formular).append("' AS KPI_CODE,").append(genJSMathSql(acctDate,formular))
                .append("\nFROM ").append(kpiConfMap.get("DST_ENTITY_OWNER")).append(".").append(dstTable);
        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成基础指标衍生值的 计算指标union all部分来源指标是计算指标的select语句
     * @Date: 2022/11/20 18:34
     * @Param: []
     * @return: java.lang.String
     */
    public static String genJSUnionJSSelectSQL(String acctDate,String formular,String tableOwner,String dstTable,List<String> dstColList){
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("\nSELECT\n\t");
        //在UNION ALL的最内层应当将拼接的账期写死
        dstColList.stream().forEach(dstCol->{
            if ("DATE_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate).append("' AS DATE_ID,\n\t");
            }else if ("MONTH_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate.substring(0,6)).append("' AS MONTH_ID,\n\t");
            }else if ("DAY_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate.substring(6,8)).append("' AS DAY_ID,\n\t");
            }else {
                selectSql.append(dstCol).append(",\n\t");
            }
        });
        selectSql.append("'").append(formular).append("' AS KPI_CODE,")
                .append(genJSMathSql(acctDate,formular))
                .append("\nFROM ").append(tableOwner).append(".").append(dstTable);
        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成基础指标衍生值的 计算指标union all部分来源指标是计算指标的select语句
     * @Date: 2022/11/20 18:34
     * @Param: []
     * @return: java.lang.String
     */
    public static String genJSUnionJSSelectSQL(String acctDate,String formular,HashMap<String,HashMap<String,String>> rmKpiConfMap,HashMap<String,Object> rmEtlconfMap){
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("\nSELECT\n\t");
        String kpiCode = CommonFun.getSplitByIndex(formular,0);
        String dstTableOwner = rmKpiConfMap.get(kpiCode).get("DST_ENTITY_OWNER");
        String dstTableName = rmKpiConfMap.get(kpiCode).get("DST_ENTITY_NAME");
        Object etlConfObject = rmEtlconfMap.get(kpiCode);
        //修改拼接UNION ALL最内层语句，select账期部分应当写死的问题
        if (etlConfObject instanceof List){
            ((List) etlConfObject).forEach(dstCol->{
                if ("DATE_ID".equals(dstCol)){
                    selectSql.append("'").append(acctDate).append("' AS DATE_ID,\n\t");
                }else if ("MONTH_ID".equals(dstCol)){
                    selectSql.append("'").append(acctDate.substring(0,6)).append("' AS MONTH_ID,\n\t");
                }else if ("DAY_ID".equals(dstCol)){
                    selectSql.append("'").append(acctDate.substring(6,8)).append("' AS DAY_ID,\n\t");
                }else {
                    selectSql.append(dstCol).append(",\n\t");
                }
            });
        }else if (etlConfObject instanceof HashMap){
            ((HashMap) etlConfObject).keySet().forEach(key->{
                if ("DATE_ID".equals(((HashMap) etlConfObject).get(key))){
                    selectSql.append("'").append(acctDate).append("' AS DATE_ID,\n\t");
                }else if ("MONTH_ID".equals(((HashMap) etlConfObject).get(key))){
                    selectSql.append("'").append(acctDate.substring(0,6)).append("' AS MONTH_ID,\n\t");
                }else if ("DAY_ID".equals(((HashMap) etlConfObject).get(key))){
                    selectSql.append("'").append(acctDate.substring(6,8)).append("' AS DAY_ID,\n\t");
                }else {
                    selectSql.append(((HashMap) etlConfObject).get(key)).append(",\n\t");
                }
            });
        }
        selectSql.append("'").append(formular).append("' AS KPI_CODE,").append(genJSMathSql(acctDate,formular))
                .append("\nFROM ").append(dstTableOwner).append(".").append(dstTableName);
        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成基础指标衍生值的 计算指标union all部分来源指标是计算指标的select语句
     * @Date: 2022/11/20 18:34
     * @Param: []
     * @return: java.lang.String
     */
    public static String genAdbJsUnionJsSelectSql(String acctDate,String formular,HashMap<String,String> kpiConfMap,List<String> dstColList){
        String dstTable = kpiConfMap.get("DST_ENTITY_NAME");
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("\nSELECT\n\t");
        dstColList.stream().forEach(dstCol->{
            if ("DATE_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate).append("' AS DATE_ID,\n\t");
            }else if ("MONTH_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate.substring(0,6)).append("' AS MONTH_ID,\n\t");
            }else if ("DAY_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate.substring(6,8)).append("' AS DAY_ID,\n\t");
            }else {
                selectSql.append(dstCol).append(",\n\t");
            }
        });
        selectSql.append("'").append(formular).append("' AS KPI_CODE,").append(genJSMathSql(acctDate,formular))
                .append("\nFROM ").append(kpiConfMap.get("DST_ENTITY_OWNER")).append(".").append(dstTable);
        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成MC上加工的基础指标衍生值的 计算指标union all部分来源指标是计算指标的select语句
     * @Date 2023/4/3 13:13
     * @Param: [acctDate, formular, kpiConfMap, dstColList]
     * @Return: java.lang.String
     */
    public static String genMcJsUnionJsSelectSql(String acctDate,String formular,HashMap<String,String> kpiConfMap,List<String> dstColList){
        String dstTable = kpiConfMap.get("DST_ENTITY_NAME");
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("\nSELECT\n\t");
        dstColList.stream().forEach(dstCol->{
            if ("DATE_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate).append("' AS DATE_ID,\n\t");
            }else if ("MONTH_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate.substring(0,6)).append("' AS MONTH_ID,\n\t");
            }else if ("DAY_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate.substring(6,8)).append("' AS DAY_ID,\n\t");
            }else {
                selectSql.append(dstCol).append(",\n\t");
            }
        });
        selectSql.append("'").append(formular).append("' AS KPI_CODE,").append(genJSMathSql(acctDate,formular))
                .append("\nFROM ").append("ITSY_CUBE.").append(dstTable);
        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成计算指标union all部分来源指标是计算指标的select语句
     * @Date: 2022/11/20 18:34
     * @Param: []
     * @return: java.lang.String
     */
    public static String genJSUnionJSSelectSQL(String acctDate,String formular,HashMap<String,String> kpiConfMap,List<String> dstColList){
        String dstTable = kpiConfMap.get("DST_ENTITY_NAME");
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("\nSELECT\n\t");
        dstColList.stream().forEach(dstCol->{
            if ("DATE_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate).append("' AS DATE_ID,\n\t");
            }else if ("MONTH_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate.substring(0,6)).append("' AS MONTH_ID,\n\t");
            }else if ("DAY_ID".equals(dstCol)){
                selectSql.append("'").append(acctDate.substring(6,8)).append("' AS DAY_ID,\n\t");
            }else {
                selectSql.append(dstCol).append(",\n\t");
            }
        });
        selectSql.append("'").append(formular).append("' AS KPI_CODE,").append(genJSMathSql(acctDate,formular))
                .append("\nFROM ").append(kpiConfMap.get("DST_ENTITY_OWNER")).append(".").append(dstTable).append("_MID");
        return selectSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据指标公式，依据《计算指标规则.docx》对公式进行解析，返回解析后的SQL计算语句
     * @Date: 2022/11/22 9:15
     * @Param: [acctDate, formular
     * @return: java.lang.String
     */
    public static String genJSMathSql(String acctDate,String formular) {
        //计算类型：TYPE 告诉计算类型是指定程序的处理方式，NOR表示时间的移动，SUM表示时间范围的累计，AVG表示时间范围的平均值, NIO表示时间范围的净增或累计净增。
        String jsType = CommonFun.getSplitByIndex(formular,1);
        //时间范围：年
        String year = CommonFun.getSplitByIndex(formular,2);
        //时间范围：月
        String month = CommonFun.getSplitByIndex(formular,3);
        //时间范围：日
        String day = CommonFun.getSplitByIndex(formular,4);
        //定义返回结果
        StringBuilder mathSql = new StringBuilder();
        long timeDistance = CommonFun.getTimeDistance(acctDate, formular);

        switch (jsType){
            case "NOR":
            case "SUM":
                mathSql.append("\n\tSUM(NVL(KPI_VALUE,0)) AS KPI_VALUE");
                break;
            case "AVG":
                mathSql.append("\n\tSUM(NVL(KPI_VALUE,0))/").append(timeDistance+1).append(" AS KPI_VALUE");
                break;
            case "NIO":
                mathSql.append("\n\tSUM(NVL(KPI_VALUE,0)) - LAG(SUM(NVL(KPI_VALUE,0))) OVER(PARTITION BY PROV_ID,AREA_ID ORDER BY MONTH_ID ASC) AS KPI_VALUE");
                break;
            case "VD":
                mathSql.append("\n\tsum(nvl(kpi_value,0))/(SELECT TO_CHAR(LAST_DAY(TO_DATE(").append(acctDate).append(",'yyyyMM','dd') KPI_VALUE FROM DUAL) AS KPI_VALUE ");
                break;
            case "VDL":
                mathSql.append("\n\tsum(nvl(kpi_value,0))/(SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(").append(acctDate)
                        .append(",'yyyyMM'),-1)),'dd') KPI_VALUE FROM DUAL) AS KPI_VALUE FROM")
                        .append("来源表 WHERE MONTH_ID = TO_CHAR(ADD_MONTHS(TO_DATE(' || acctDate ||',''yyyyMM''),-1),''yyyyMM'')");
                break;
            default:
                break;
        }
        return mathSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据指标公式，依据《计算指标规则.docx》对公式进行解析，返回解析后的SQL条件限定语句
     * @Date: 2022/11/22 10:51
     * @Param: [acctDate, formular]
     * @return: java.lang.String
     */
    public static String genJSWhereSql(String acctDate,String formular,String kpiExplain) {
        //
        formular = CommonCheck.checkAndFormatFormular(formular);
        //条件关键词
        String dateType = CommonFun.dateType(acctDate);
        //计算类型：TYPE 告诉计算类型是指定程序的处理方式，NOR表示时间的移动，SUM表示时间范围的累计，AVG表示时间范围的平均值, NIO表示时间范围的净增或累计净增。
        String jsType = CommonFun.getSplitByIndex(formular,1);
        //根据公式解析的标志性账期值
        String transferBeginDate = CommonFun.genSpeBeginDate(acctDate,formular);
        String transferEndDate = CommonFun.genSpeEndDate(acctDate,formular);
        //定义返回结果
        StringBuilder whereSql = new StringBuilder();
        if ("NOR".equals(jsType)){
            whereSql.append("\nWHERE ").append(dateType).append(" = '").append(transferBeginDate).append("'");
        }else if ("SUM".equals(jsType)||"AVG".equals(jsType)||"NIO".equals(jsType)){
            whereSql.append("\nWHERE ").append(dateType).append(" BETWEEN '").append(transferBeginDate).append("' AND '").append(transferEndDate).append("'");
        }else{
            throw new MyException("公式配置错误：不支持的计算类型。");
        }
        if ("计算时不剔除汇总值".equals(kpiExplain)){
            whereSql.append("\n\tAND KPI_CODE = '").append(CommonFun.getSplitByIndex(formular,0)).append("'");
        }else {
            whereSql.append("\n\tAND KPI_CODE = '").append(CommonFun.getSplitByIndex(formular,0)).append("'")
                    .append("\n\tAND AREA_ID != '-1'");
        }
        return  whereSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据指标公式，依据《计算指标规则.docx》对公式进行解析，返回解析后的SQL条件限定语句
     * @Date: 2022/11/22 10:51
     * @Param: [acctDate, formular]
     * @return: java.lang.String
     */
    public static String genJSJSWhereSql(String acctDate,String formular) {
        //
        formular = CommonCheck.checkAndFormatFormular(formular);
        //条件关键词
        String dateType = CommonFun.dateType(acctDate);
        //计算类型：TYPE 告诉计算类型是指定程序的处理方式，NOR表示时间的移动，SUM表示时间范围的累计，AVG表示时间范围的平均值, NIO表示时间范围的净增或累计净增。
        String jsType = CommonFun.getSplitByIndex(formular,1);
        //根据公式解析的标志性账期值
        String transferBeginAcctDate = CommonFun.genSpeBeginDate(acctDate,formular);
        String transferEndAcctDate = CommonFun.genSpeEndDate(acctDate,formular);
        //定义返回结果
        StringBuilder whereSql = new StringBuilder();
        if ("NOR".equals(jsType)){
            whereSql.append("\nWHERE ").append(dateType).append(" = '").append(transferBeginAcctDate).append("'");
        }else if ("SUM".equals(jsType)||"AVG".equals(jsType)||"NIO".equals(jsType)){
            whereSql.append("\nWHERE ").append(dateType).append(" BETWEEN '").append(transferBeginAcctDate).append("' AND '").append(transferEndAcctDate).append("'");
        }else{
            throw new MyException("公式配置错误：不支持的计算类型。");
        }
        whereSql.append("\n\tAND KPI_CODE = '").append(CommonFun.getSplitByIndex(formular,0)).append("'");
        return  whereSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 根据公式内容，解析生成计算指标最内层 UNION ALL语句的一部分
     * @Date: 2022/11/23 10:15
     * @Param: [formular, srcColMap, dstTable]
     * @return: java.lang.String
     */
    public static String genJSUnionSql(String acctDate, String formular, Properties confProp,List<String> dstColList,String kpiExplain){
        StringBuilder res = new StringBuilder();
        String kpiCode = CommonFun.getSplitByIndex(formular,0);
        HashMap<String, String> kpiConfMap = CommonFun.getKpiConf(confProp, kpiCode);
        res.append(genJSUnionJSSelectSQL(acctDate,formular,kpiConfMap,dstColList))
                .append(genJSWhereSql(acctDate,formular,kpiExplain))
                .append(genJSUnionGroupBySql(acctDate,dstColList));
        return res.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description:  根据公式内容，解析生成计算指标最内层 UNION ALL语句的一部分（通过同一个连接对象进行多个公式对应指标编码的配置查询）
     * @Date: 2022/11/26 15:38
     * @Param: [acctDate, formular, conn]
     * @return: java.lang.String
     */
    public static String genJSUnionSql(String acctDate, String formular,String tableOwner,String tableName,String kpiExplain){
        StringBuilder res = new StringBuilder();
        String kpiCode = CommonFun.getSplitByIndex(formular,0);
        HashMap<Integer, String> jsEtlConf = new HashMap<>();
        //基础指标调度写死的公式时，不需要检测指标配置信息，必然可以执行，只需要获取元数据中目标表的列信息即可
        List<String> dstColList = CommonCheck.dstColList;
        res.append(genJSUnionJSSelectSQL(acctDate,formular,tableOwner,tableName,dstColList))
                .append(genJSWhereSql(acctDate,formular,kpiExplain))
                .append(genJSUnionGroupBySql(acctDate,dstColList));
               // .append(genGroupBySql(dstColList));
        return res.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 通过外层传递进来的去重后的KPI_CODE所有配置信息，直接进行公式解析拼接
     * @Date: 2022/11/26 16:45
     * @Param: [acctDate, formular, rmkpiConfMap, rmEtlConfMap]
     * @return: java.lang.String
     */
    public static String genJSUnionSql(String acctDate, String formular,HashMap<String, HashMap<String,String>> rmkpiConfMap,HashMap<String, HashMap<Integer,String>> rmEtlConfMap,Connection conn){
        StringBuilder res = new StringBuilder();
        HashMap<String, String> kpiConfMap = rmkpiConfMap.get(CommonFun.getSplitByIndex(formular, 0));
        HashMap<Integer, String> jsEtlConf = rmEtlConfMap.get(CommonFun.getSplitByIndex(formular, 0));
        res.append(genJSUnionJSSelectSQL(acctDate,formular,kpiConfMap,jsEtlConf,conn))
                .append(genJSWhereSql(acctDate,formular,kpiConfMap.get("KPI_EXPLAIN")))
                .append(genGroupBySql(jsEtlConf));
        return res.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 通过外层传递进来的去重后的 KPI_CODE 所有配置信息，判断是自动匹配的映射关系或手动配置的ETL，进行公式解析拼接
     * @Date: 2022/12/2 14:58
     * @Param: [acctDate, formular, rmkpiConfMap, rmEtlConfMap]
     * @return: java.lang.String
     */
    public static String genJSUnionSql(String acctDate, String formular,HashMap<String,String> kpiConfMap,List<String> dstColList){
        StringBuilder res = new StringBuilder();
        res.append(genAdbJsUnionJsSelectSql(acctDate,formular,kpiConfMap,dstColList))
                .append(genJSJSWhereSql(acctDate,formular))
                .append(genJSUnionGroupBySql(acctDate,dstColList));
        return res.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: Mc上通过外层传递进来的去重后的 KPI_CODE 所有配置信息，判断是自动匹配的映射关系或手动配置的ETL，进行公式解析拼接
     * @Date 2023/4/3 13:16
     * @Param: [acctDate, formular, kpiConfMap, dstColList]
     * @Return: java.lang.String
     */
    public static String genMcJSUnionSql(String acctDate, String formular,HashMap<String,String> kpiConfMap,List<String> dstColList){
        StringBuilder res = new StringBuilder();
        res.append(genMcJsUnionJsSelectSql(acctDate,formular,kpiConfMap,dstColList))
                .append(genJSJSWhereSql(acctDate,formular))
                .append(genJSUnionGroupBySql(acctDate,dstColList));
        return res.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成内层union all拼接的select语句
     * @Date: 2022/11/23 19:54
     * @Param: [acctDate, kpiConfMap, confProp]
     * @return: java.lang.String
     */
    public static String genJSUnionAllSql(String acctDate,HashMap<String,String> kpiConfMap,Properties confProp){
        String res = "";
        //生成计算指标可执行语句方法
        List<String> formularList = CommonFun.getFormularList(kpiConfMap.get(FORMULATION));
        //对指标公式列表进行去重
        List<String> rmformularList = CommonFun.removeDuplicationByContains(formularList);
        List<String> dstColList = CommonCheck.dstColList;
        for(int i=0;i<rmformularList.size();i++){
            if (i<rmformularList.size()-1){
                res = res + CommonSql.genJSUnionSql(acctDate,rmformularList.get(i),confProp,dstColList,kpiConfMap.get("KPI_EXPLAIN"))+"\n\nUNION ALL\n";
            }else {
                res = res + CommonSql.genJSUnionSql(acctDate,rmformularList.get(i),confProp,dstColList,kpiConfMap.get("KPI_EXPLAIN"));
            }
        }
        return res;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成内层union all拼接的select语句,带有自定义衍生值公式部分的UNION ALL部分
     * @Date: 2022/11/23 19:54
     * @Param: [acctDate, kpiConfMap, confProp]
     * @return: java.lang.String
     */
    //public static String genBaseDefineUnionAllSql(String acctDate,HashMap<String,String> kpiConfMap,HashMap<String,HashMap<Integer,String>> etlConfMap,Properties confProp){
    //    String res = genBaseSql(acctDate,kpiConfMap,etlConfMap)+"\n\nUNION ALL\n";
    //    //生成计算指标可执行语句方法
    //    List<String> formularList = CommonFun.getFormulaList(kpiConfMap.get(FORMULATION));
    //    //对指标公式列表进行去重
    //    List<String> rmformularList = CommonFun.removeDuplicationByContains(formularList);
    //    rmformularList.addAll(genDefineFormularList(acctDate,kpiConfMap));
    //    for(int i=0;i<rmformularList.size();i++){
    //        if (i<rmformularList.size()-1){
    //            res = res + CommonSql.genJSUnionSql(acctDate,rmformularList.get(i),confProp)+"\n\nUNION ALL\n";
    //        }else {
    //            res = res + CommonSql.genJSUnionSql(acctDate,rmformularList.get(i),confProp);
    //        }
    //    }
    //    return res;
    //}
    //

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成内层union all拼接的select语句,带有自定义衍生值公式部分的UNION ALL部分(复用JDBC连接对象）
     * @Date: 2022/11/23 19:54
     * @Param: [acctDate, kpiConfMap, confProp]
     * @return: java.lang.String
     */
    public static String genBaseMcDefineUnionAllSql(String acctDate,HashMap<String,String> kpiConfMap,HashMap<String,HashMap<Integer,String>> etlConfMap){
        String res = genMcBaseSql(acctDate,kpiConfMap,etlConfMap)+"\n\nUNION ALL\n";
        //生成计算指标可执行语句方法
        List<String> formularList = CommonFun.getFormularList(kpiConfMap.get(FORMULATION));
        //对指标公式列表进行去重
        List<String> rmformularList = CommonFun.removeDuplicationByContains(formularList);
        //根据是否为时期值判断自定义公式列表
        if("1".equals(kpiConfMap.get("IS_SUM"))){
            rmformularList.addAll(genDefineFormularList(acctDate,kpiConfMap));
        }else {
            rmformularList.addAll(genDefineIsNotSumFormularList(acctDate,kpiConfMap));
        }
        for(int i=0;i<rmformularList.size();i++){
            if (i<rmformularList.size()-1){
                res = res + CommonSql.genJSUnionSql(acctDate,rmformularList.get(i),kpiConfMap.get("DST_ENTITY_OWNER"),kpiConfMap.get("DST_ENTITY_NAME"),kpiConfMap.get("KPI_EXPLAIN"))+"\n\nUNION ALL\n";
            }else {
                res = res + CommonSql.genJSUnionSql(acctDate,rmformularList.get(i),kpiConfMap.get("DST_ENTITY_OWNER"),kpiConfMap.get("DST_ENTITY_NAME"),kpiConfMap.get("KPI_EXPLAIN"));
            }
        }
        return res;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成内层union all拼接的select语句,带有自定义衍生值公式部分的UNION ALL部分(复用JDBC连接对象）
     * @Date: 2022/11/23 19:54
     * @Param: [acctDate, kpiConfMap, confProp]
     * @return: java.lang.String
     */
    public static String genBaseDefineUnionAllSql(String acctDate,HashMap<String,String> kpiConfMap,HashMap<String,HashMap<Integer,String>> etlConfMap){
        String res = genAdbBaseSql(acctDate,kpiConfMap,etlConfMap)+"\n\nUNION ALL\n";
        //生成计算指标可执行语句方法
        List<String> formularList = CommonFun.getFormularList(kpiConfMap.get(FORMULATION));
        //对指标公式列表进行去重
        List<String> rmformularList = CommonFun.removeDuplicationByContains(formularList);
        //根据是否为时期值判断自定义公式列表
        if("1".equals(kpiConfMap.get("IS_SUM"))){
            rmformularList.addAll(genDefineNotContainsCurrentAvgFormularList(acctDate,kpiConfMap));
        }else {
            rmformularList.addAll(genDefineIsNotSumNotContainsCurrentAvgFormularList(acctDate,kpiConfMap));
        }
        for(int i=0;i<rmformularList.size();i++){
            if (i<rmformularList.size()-1){
                res = res + CommonSql.genJSUnionSql(acctDate,rmformularList.get(i),kpiConfMap.get("DST_ENTITY_OWNER"),kpiConfMap.get("DST_ENTITY_NAME"),kpiConfMap.get("KPI_EXPLAIN"))+"\n\nUNION ALL\n";
            }else {
                res = res + CommonSql.genJSUnionSql(acctDate,rmformularList.get(i),kpiConfMap.get("DST_ENTITY_OWNER"),kpiConfMap.get("DST_ENTITY_NAME"),kpiConfMap.get("KPI_EXPLAIN"));
            }
        }
        return res;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成内层union all拼接的select语句,带有自定义衍生值公式部分的UNION ALL部分(复用JDBC连接对象）
     * @Date: 2022/11/23 19:54
     * @Param: [acctDate, kpiConfMap, confProp]
     * @return: java.lang.String
     */
    public static String genMcBaseDefineUnionAllSql(String acctDate,HashMap<String,String> kpiConfMap,HashMap<String,HashMap<Integer,String>> etlConfMap){
        String res = genMcBaseSql(acctDate,kpiConfMap,etlConfMap)+"\n\nUNION ALL\n";
        //生成计算指标可执行语句方法
        List<String> formularList = CommonFun.getFormularList(kpiConfMap.get(FORMULATION));
        //对指标公式列表进行去重
        List<String> rmformularList = CommonFun.removeDuplicationByContains(formularList);
        //根据是否为时期值判断自定义公式列表
        if("1".equals(kpiConfMap.get("IS_SUM"))){
            rmformularList.addAll(genDefineNotContainsCurrentAvgFormularList(acctDate,kpiConfMap));
        }else {
            rmformularList.addAll(genDefineIsNotSumNotContainsCurrentAvgFormularList(acctDate,kpiConfMap));
        }
        for(int i=0;i<rmformularList.size();i++){
            if (i<rmformularList.size()-1){
                res = res + CommonSql.genJSUnionSql(acctDate,rmformularList.get(i),"ITSY_CUBE",kpiConfMap.get("DST_ENTITY_NAME"),kpiConfMap.get("KPI_EXPLAIN"))+"\n\nUNION ALL\n";
            }else {
                res = res + CommonSql.genJSUnionSql(acctDate,rmformularList.get(i),"ITSY_CUBE",kpiConfMap.get("DST_ENTITY_NAME"),kpiConfMap.get("KPI_EXPLAIN"));
            }
        }
        return res;
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成内层union all拼接的select语句,带有自定义衍生值公式部分的UNION ALL部分
     * @Date: 2022/11/23 19:54
     * @Param: [acctDate, kpiConfMap, confProp]
     * @return: java.lang.String
     */
    public static String genJSDefineUnionAllSql(String acctDate,HashMap<String,String> kpiConfMap,Properties confProp){
        Connection conn = MyJdbcUtils.getADBConnection(confProp);
        StringBuilder res = new StringBuilder();
        //生成计算指标可执行语句方法
        List<String> formularList = CommonFun.getFormularList(kpiConfMap.get(FORMULATION));
        //对指标公式列表进行去重
        List<String> rmformularList = CommonFun.removeDuplicationByContains(formularList);
        //根据是否为时期值判断自定义公式列表
        if("1".equals(kpiConfMap.get("IS_SUM"))){
            rmformularList.addAll(genDefineNotContainsCurrentAvgFormularList(acctDate,kpiConfMap));
        }else {
            rmformularList.addAll(genDefineIsNotSumNotContainsCurrentAvgFormularList(acctDate,kpiConfMap));
        }
        rmformularList.stream().limit(rmformularList.size()-1).forEach(formular->{
             res.append(CommonSql.genJSUnionSql(acctDate,formular,kpiConfMap,CommonCheck.dstColList)).append("\n\nUNION ALL\n");
        });
        rmformularList.stream().skip(rmformularList.size()-1).findFirst().ifPresent(formular->{
            res.append(CommonSql.genJSUnionSql(acctDate,formular,kpiConfMap,CommonCheck.dstColList));
        });
        MyJdbcUtils.close(conn);
        return res.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成内层union all拼接的select语句,带有自定义衍生值公式部分的UNION ALL部分
     * @Date: 2022/11/23 19:54
     * @Param: [acctDate, kpiConfMap, confProp]
     * @return: java.lang.String
     */
    public static String genMcJSDefineUnionAllSql(String acctDate,HashMap<String,String> kpiConfMap,Properties confProp){
        Connection conn = MyJdbcUtils.getADBConnection(confProp);
        StringBuilder res = new StringBuilder();
        //生成计算指标可执行语句方法
        List<String> formularList = CommonFun.getFormularList(kpiConfMap.get(FORMULATION));
        //对指标公式列表进行去重
        List<String> rmformularList = CommonFun.removeDuplicationByContains(formularList);
        //根据是否为时期值判断自定义公式列表
        if("1".equals(kpiConfMap.get("IS_SUM"))){
            rmformularList.addAll(genDefineNotContainsCurrentAvgFormularList(acctDate,kpiConfMap));
        }else {
            rmformularList.addAll(genDefineIsNotSumNotContainsCurrentAvgFormularList(acctDate,kpiConfMap));
        }
        rmformularList.stream().limit(rmformularList.size()-1).forEach(formular->{
             res.append(CommonSql.genMcJSUnionSql(acctDate,formular,kpiConfMap,CommonCheck.dstColList)).append("\n\nUNION ALL\n");
        });
        rmformularList.stream().skip(rmformularList.size()-1).findFirst().ifPresent(formular->{
            res.append(CommonSql.genMcJSUnionSql(acctDate,formular,kpiConfMap,CommonCheck.dstColList));
        });
        MyJdbcUtils.close(conn);
        return res.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成计算指标行列转换部分的Select部分
     * @Date: 2022/11/23 19:57
     * @Param: []
     * @return: java.lang.String
     */
    public static String genJSConversionSelectSql(HashMap<String,String> kpiConfMap,HashMap<String,HashMap<Integer,String>> etlConfMap){
        StringBuilder res = new StringBuilder();
        List<String> formularList = CommonFun.getFormularList(kpiConfMap.get(FORMULATION));
        //对指标公式列表进行去重
        List<String> rmformularList = CommonFun.removeDuplicationByContains(formularList);
        res.append("SELECT \n\t");
        for (int i=0;i<etlConfMap.get("dstColMap").size();i++){
            res.append(etlConfMap.get("dstColMap").get(i)).append(",\n\t");
        }
        for (int i=0;i<rmformularList.size();i++){
            if (i<rmformularList.size()-1){
                res.append("SUM(CASE WHEN KPI_CODE = '")
                        .append(rmformularList.get(i))
                        .append("' THEN KPI_VALUE ELSE NULL END) AS '")
                        .append(rmformularList.get(i)).append("',\n\t");
            }else {
                res.append("SUM(CASE WHEN KPI_CODE = '")
                        .append(rmformularList.get(i))
                        .append("' THEN KPI_VALUE ELSE NULL END) AS '")
                        .append(rmformularList.get(i)).append("'");
            }
        }
        res.append("\nFROM (");
        return res.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成基础指标行列转换部分的Select部分,带有自定义衍生值公式部分
     * @Date: 2022/11/25 11:49
     * @Param: [acctDate, kpiConfMap, etlConfMap]
     * @return: java.lang.String
     */
    public static String genBaseDefineConversionSelectSql(String acctDate,HashMap<String,String> kpiConfMap,HashMap<String,HashMap<Integer,String>> etlConfMap){
        StringBuilder res = new StringBuilder();
        List<String> formularList = CommonFun.getFormularList(kpiConfMap.get(FORMULATION));
        //对指标公式列表进行去重
        List<String> rmformularList = CommonFun.removeDuplicationByContains(formularList);
        //根据是否为时期值，拼接不同的自定义公式列表
        if("1".equals(kpiConfMap.get("IS_SUM"))){
            rmformularList.addAll(genDefineNotContainsCurrentAvgFormularList(acctDate,kpiConfMap));
        }else {
            rmformularList.addAll(genDefineIsNotSumNotContainsCurrentAvgFormularList(acctDate,kpiConfMap));
        }
        res.append("SELECT \n\t");
        if (CommonCheck.isRelationMap){
            CommonCheck.dstColList.stream().forEach(dstCol->{
                res.append(dstCol).append(",\n\t");
            });
        }else {
            for (int i=0;i<etlConfMap.get("dstColMap").size();i++){
                res.append(etlConfMap.get("dstColMap").get(i)).append(",\n\t");
            }

        }
        res.append("SUM(CASE WHEN KPI_CODE = '").append(kpiConfMap.get("KPI_CODE"))
                .append("' THEN KPI_VALUE ELSE NULL END) AS '").append(kpiConfMap.get("KPI_CODE")).append("',\n\t");
        for (int i=0;i<rmformularList.size();i++){
            if (i<rmformularList.size()-1){
                res.append("SUM(CASE WHEN KPI_CODE = '")
                        .append(rmformularList.get(i))
                        .append("' THEN KPI_VALUE ELSE NULL END) AS '")
                        .append(rmformularList.get(i)).append("',\n\t");
            }else {
                res.append("SUM(CASE WHEN KPI_CODE = '")
                        .append(rmformularList.get(i))
                        .append("' THEN KPI_VALUE ELSE NULL END) AS '")
                        .append(rmformularList.get(i)).append("'");
            }
        }
        res.append("\nFROM (");
        return res.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成基础指标行列转换部分的Select部分,带有自定义衍生值公式部分（MC上进行加工）
     * @Date 2023/4/1 21:12
     * @Param: [acctDate, kpiConfMap, etlConfMap]
     * @Return: java.lang.String
     */
    public static String genMcBaseDefineConversionSelectSql(String acctDate,HashMap<String,String> kpiConfMap,HashMap<String,HashMap<Integer,String>> etlConfMap){
        StringBuilder res = new StringBuilder();
        List<String> formularList = CommonFun.getFormularList(kpiConfMap.get(FORMULATION));
        //对指标公式列表进行去重
        List<String> rmformularList = CommonFun.removeDuplicationByContains(formularList);
        //根据是否为时期值，拼接不同的自定义公式列表
        if("1".equals(kpiConfMap.get("IS_SUM"))){
            rmformularList.addAll(genDefineNotContainsCurrentAvgFormularList(acctDate,kpiConfMap));
        }else {
            rmformularList.addAll(genDefineIsNotSumNotContainsCurrentAvgFormularList(acctDate,kpiConfMap));
        }
        res.append("SELECT \n\t");
        if (CommonCheck.isRelationMap){
            CommonCheck.dstColList.stream().forEach(dstCol->{
                res.append(dstCol).append(",\n\t");
            });
        }else {
            for (int i=0;i<etlConfMap.get("dstColMap").size();i++){
                res.append(etlConfMap.get("dstColMap").get(i)).append(",\n\t");
            }

        }
        res.append("SUM(CASE WHEN KPI_CODE = '").append(kpiConfMap.get("KPI_CODE"))
                .append("' THEN KPI_VALUE ELSE NULL END) AS `").append(kpiConfMap.get("KPI_CODE")).append("`,\n\t");
        for (int i=0;i<rmformularList.size();i++){
            if (i<rmformularList.size()-1){
                res.append("SUM(CASE WHEN KPI_CODE = '")
                        .append(rmformularList.get(i))
                        .append("' THEN KPI_VALUE ELSE NULL END) AS `")
                        .append(rmformularList.get(i)).append("`,\n\t");
            }else {
                res.append("SUM(CASE WHEN KPI_CODE = '")
                        .append(rmformularList.get(i))
                        .append("' THEN KPI_VALUE ELSE NULL END) AS `")
                        .append(rmformularList.get(i)).append("` \n");
            }
        }
        res.append("FROM (");
        return res.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成基础指标行列转换部分的Select部分,带有自定义衍生值公式部分
     * @Date: 2022/11/25 11:49
     * @Param: [acctDate, kpiConfMap, etlConfMap]
     * @return: java.lang.String
     */
    public static String genBaseMC2AdbDefineConversionSelectSql(String acctDate, HashMap<String,String> kpiConfMap, HashMap<String,HashMap<Integer,String>> etlConfMap){
        StringBuilder res = new StringBuilder();
        List<String> formularList = CommonFun.getFormularList(kpiConfMap.get(FORMULATION));
        //对指标公式列表进行去重
        List<String> rmformularList = CommonFun.removeDuplicationByContains(formularList);
        //根据是否为时期值，拼接不同的自定义公式列表
        if("1".equals(kpiConfMap.get("IS_SUM"))){
            rmformularList.addAll(genDefineFormularList(acctDate,kpiConfMap));
        }else {
            rmformularList.addAll(genDefineIsNotSumFormularList(acctDate,kpiConfMap));
        }
        res.append("SELECT \n\t");
        if (CommonCheck.isRelationMap){
            CommonCheck.dstColList.stream().forEach(dstCol->{
                res.append(dstCol).append(",\n\t");
            });
        }else {
            for (int i=0;i<etlConfMap.get("dstColMap").size();i++){
                res.append(etlConfMap.get("dstColMap").get(i)).append(",\n\t");
            }

        }
        res.append("SUM(CASE WHEN KPI_CODE = '").append(kpiConfMap.get("KPI_CODE"))
                .append("' THEN KPI_VALUE ELSE NULL END) AS '").append(kpiConfMap.get("KPI_CODE")).append("',\n\t");
        for (int i=0;i<rmformularList.size();i++){
            if (i<rmformularList.size()-1){
                res.append("SUM(CASE WHEN KPI_CODE = '")
                        .append(rmformularList.get(i))
                        .append("' THEN KPI_VALUE ELSE NULL END) AS '")
                        .append(rmformularList.get(i)).append("',\n\t");
            }else {
                res.append("SUM(CASE WHEN KPI_CODE = '")
                        .append(rmformularList.get(i))
                        .append("' THEN KPI_VALUE ELSE NULL END) AS '")
                        .append(rmformularList.get(i)).append("'");
            }
        }
        res.append("\nFROM (");
        return res.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 生成计算指标行列转换部分的Select部分,带有自定义衍生值公式部分
     * @Date: 2022/11/23 19:57
     * @Param: []
     * @return: java.lang.String
     */
    public static String genJSDefineConversionSelectSql(String acctDate,HashMap<String,String> kpiConfMap,HashMap<Integer,String> jsEtlConf){
        StringBuilder res = new StringBuilder();
        List<String> formularList = CommonFun.getFormularList(kpiConfMap.get(FORMULATION));
        //对指标公式列表进行去重
        List<String> rmformularList = CommonFun.removeDuplicationByContains(formularList);
        //根据是否为时期值，拼接不同的自定义公式列表
        if("1".equals(kpiConfMap.get("IS_SUM"))){
            rmformularList.addAll(genDefineNotContainsCurrentAvgFormularList(acctDate,kpiConfMap));
        }else {
            rmformularList.addAll(genDefineIsNotSumNotContainsCurrentAvgFormularList(acctDate,kpiConfMap));
        }
        res.append("SELECT \n\t");
        if (CommonCheck.isRelationMap){
            CommonCheck.dstColList.stream().forEach(dstCol->{
                res.append(dstCol).append(",\n\t");
            });
        }else {
            for (int i=0;i<jsEtlConf.size();i++){
                res.append(jsEtlConf.get(i)).append(",\n\t");
            }
        }
        for (int i=0;i<rmformularList.size();i++){
            if (i<rmformularList.size()-1){
                res.append("SUM(CASE WHEN KPI_CODE = '")
                        .append(rmformularList.get(i))
                        .append("' THEN KPI_VALUE ELSE NULL END) AS `")
                        .append(rmformularList.get(i)).append("`,\n\t");
            }else {
                res.append("SUM(CASE WHEN KPI_CODE = '")
                        .append(rmformularList.get(i))
                        .append("' THEN KPI_VALUE ELSE NULL END) AS `")
                        .append(rmformularList.get(i)).append("`");
            }
        }
        res.append("\nFROM (");
        return res.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description:
     * @Date: 2023/2/13 15:37
     * @Param: [acctDate, kpiConfMap, confProp]
     * @return: java.lang.String
     */
    public static String genBaseMapExecuteSql(String acctDate, HashMap<String, String> kpiConfMap, Properties confProp) {
        //生成删除历史账期的语句
        CommonSql.genDeleteExecuteSql(acctDate, kpiConfMap);
        CommonPrint.printBaseSqlStart();
        StringBuilder executeSql = new StringBuilder();
        HashMap<String, HashMap<Integer, String>> etlConfMap = CommonCheck.etlConfMap;
        executeSql.append(genDefineInsertSQL(acctDate,kpiConfMap,etlConfMap.get("dstColMap")))
                .append(genBaseMapSelectSql(acctDate,kpiConfMap,etlConfMap,confProp))
                .append(genBaseWhereSql(acctDate,kpiConfMap))
                .append(genGroupBySql(etlConfMap.get("srcColMap"))).append("\n;");
        return executeSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description:
     * @Date: 2023/2/13 15:37
     * @Param: [acctDate, kpiConfMap, confProp]
     * @return: java.lang.String
     */
    public static String genMcBaseMapExecuteSql(String acctDate, HashMap<String, String> kpiConfMap, Properties confProp) {
        StringBuilder executeSql = new StringBuilder();
        executeSql.append(genAddPartMcExecuteSql(acctDate,kpiConfMap));
        HashMap<String, HashMap<Integer, String>> etlConfMap = CommonCheck.etlConfMap;
        executeSql.append(genMcDefineInsertOverwriteSQL(acctDate,kpiConfMap,etlConfMap.get("dstColMap")))
                .append(genMcBaseMapSelectSql(acctDate,kpiConfMap,etlConfMap,confProp))
                .append(genBaseWhereSql(acctDate,kpiConfMap))
                .append(genGroupBySql(etlConfMap.get("srcColMap"))).append(";\n");
        return executeSql.toString();
    }

    /**
     * @Author: china-fanxin
     * @Website: https://fan-xin.cn
     * @Description: 由于MC上的指标中间表和配置表会不一致，所以需要管理MC上进行指标加工的目标表名
     * @Date 2023/4/2 21:22
     * @Param: [kpiConfMap]
     * @Return: java.lang.String
     */
    public static String genMcTransferTableSql(HashMap<String, String> kpiConfMap){
        StringBuilder res = new StringBuilder();
        if ("DM_D_KPI_AL_COMP_S".equals(kpiConfMap.get("DST_ENTITY_NAME"))){
            res.append("ITSY_CUBE.DM_D_KPI_AL_COMP_SUB");
        }else if ("DM_M_KPI_AL_COMP_S".equals(kpiConfMap.get("DST_ENTITY_NAME"))){
            res.append("ITSY_CUBE.DM_M_KPI_AL_COMP_SUB");
        } else {
            res.append("ITSY_CUBE.").append(kpiConfMap.get("DST_ENTITY_NAME"));
        }
        return res.toString();
    }
}